In my SQL 2005 database table Records, I have 3 fields, field1, field2, and field3 which are all nvarchar(50) fields. The value of field2 is something like this, MDB006-MD002-0004-3-2007. I would like to order this field but only use the 0004-3-2007 part of the field to order it. Is it possible to put the last 11 charachters (0004-3-2007) in another field and then order it using this new field?
View 8 RepliesI have a very strange situation. I've increased the size of an NVARCHAR field from 8 to 9 in a database table. The format of the data that I enter will either be an 8 character field (123456-8) or a 9 character field (1234567-9). The '-' is critical.
It used to only accept the 8 character version, but after increasing the field size, if I try to insert the 9 character field version, it gets truncated after the '-', as though it's still only allowing 8 characters. But that only occurs when I include the '-' or other such characters like '#'. If I try to insert 1234567a9, it works. The following explains the outcomes:
Inserted Value -> Result in table
123456-8 -> 123456-8 *Correct
1234567-9 -> 1234567- *Wrong
123456789 -> 123456789 *Correct
1234567#9 -> 1234567# *Wrong
1234567a9 -> 1234567a9 *Correct
Why is it that characters such as '-' and '#' are truncating the value, but only if the string is 9 chars long?
I'm currently using a direct t-sql insert statement in SQLExpress. The field is a simple NVARCHAR(9) field.
I have a scenario to sort on an nvarchar (50) field and I need to keep any changes to the sproc in the ORDER BY clause if possible. This field will contain strings such as...
abc-217c, abc-15a, abc-9a, abc-7b, abc-17ar, etc...
The issue I'm having is when the records are sorted, they are returned as...
abc-15a, abc-17ar, abc-217c, abc-7b, abc-9a,etc...ordering numerically on the first numeric character in the string ie, 1,1,2,7,9)
However, I need the numeric component to be treated as a whole number and order in this fashion...
abc-7b, abc-9a, abc-15a, abc-17ar, abc-217c (7,9,15,17,217, etc)
I feel pretty sure that this issue has come up before...can anybody provide a working example that would provide a simple(or not so simple) solution?
I need to sort by an ntext field, but it won't let me do it.
However, if I cast the field as nvarchar(100), I can use ORDER BY on that.
Is there any reason that this is a bad idea? In my testing, ordering by a converted ntext field was actually *faster* than ordering by an nvarchar (same data in the fields).
Josh
How to sort table in sql2000 with ipaddress(format x.x.x.x) as column with nvarchar datatype in ascending order
without using stored procedure
Ex:
Table: netComputers(3 rows)
Column Name: ipAddress (string data type)
ipAddress
0.0.18.1
0.1.1.2
0.0.0.1
Sql query : if I use the query
Select ipAddress from netComputers order by cast( replace(ipaddress,'.','') as numeric(12)) asc
Gives result as :
ipAddress
0.0.0.1
0.1.1.2
0.0.18.1
Where as expected result should be:
ipAddress
0.0.0.1
0.0.18.1
0.1.1.2
i'm having following:
create table tbl_text
(id int,
sometext nvarchar(max)
)
insert into tbl_text (id, sometext) values (1, 'I have here paragraph
and here is another one,
and another
and another')
how can i tell sql server 2005 to give use all the paragraphs within sometext field?
Hi!
Is there any way to store UTF-8 encoded string in Nvarchar field? Or strings can be stored only in Unicode?
There are two fieldsA1 nvarchar(30)A2 nvarchar(800)I know nvarchar field is alterable length, if I store a string mystring='abc' to A1 field or to A2 field, I think they use the same disk space, so I think it's always a good way to define a big length nvarchar field such as A3 nvarchar(4000) for any length string, becuase they always use the same disk space, is it right?
View 1 Replies View Relatedhi
i have connected my ms sql 2000 with C using ODBC
can u help me to return the utf 8 string from nvarchar field ??
how should i do it
please help!!!!!!!
Hi everyone.
So as the subject says, I have a few fields that are nvarchar but hold date information. Most of these fields I have been able to move to datetime easiliy enough, simply by going into edit mode for the table and converting the fields to datetime. But 1 field is giving me problems I keep getting this error.
quote:- Unable to modify table.
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.
I really dont know why I'm getting this error, but I"m assuming it may have to be something like one of the records may not be in date format. But I don't know if this is the case and I don't know how to locate where my problem is coming from.
Any guidance is greatly appreciated.
Thanks.
I have data saved in nvarchar field in SQL Server and I am displaying it using <= dbReader["my_field"] but even the three or four paragraphs comes as one paragragh and I don't know know where are my line breaks going?
View 6 Replies View RelatedWhich charset for nvarchar field in SQL 2005 database? UTF-8, UTF-16...Does normal character such as A, B, C use double-byte in nvarchar field?
View 4 Replies View RelatedI have a colum of numbers, which datatype is nvarchar;
157853
4389
1999999999556
4568987
I need to insert a hyphen, 3 spaces from the right (to left) My numbers would look like this:
157-853
4-389
1999999999-556
4568-987
...do I use the rtrim function ?...what would the syntax look like ?
Thank you
I copying data from our Informix 7.2 database into SQL Server 2K using DTS but hitting errors during the process. There appears to be date data within Informix that will not convert properly when moving into SQL. Since the error is appearing at the 1.5million (approx.) record. I figured on changing from datetime to nvarchar. Works like a charm! :-)
My new problem is converting it back to datetime so I can query against the date without having to create scripts to parse the field.
The data in SQL currently looks like this -> 2000-11-29 (nvarchar(50))
I would like to have it -> 11/29/00 (datetime)
Any help is greatly appreciated!
JT
The goodness we share is rewarded twice!
What is the correct syntax to convert a field from nvarchar to datetime?
View 2 Replies View RelatedMS SQL 2000. Does anyone know how to find all rows where an nvarchar column contains a specific unicode character? Is it possible without creating a user defined function? Here's the issue. I have a table Expression (ExpID, ExpText) with values like 'x < 100' and 'y ≤ 200'. where the second example contains Unicode character 8804 [that is, nchar(8804)]. Because it's unicode, I don't seem to be able to search for it with LIKE or PATINDEX. These fail:
SELECT * FROM Expression WHERE ExpText LIKE '%≤%' -- no recordsSELECT * FROM Expression WHERE PATINDEX('%≤%', ExpText) -- no records
However, SELECT PATINDEX('%≤%', 'y ≤ 200') will return 3.
Any suggestions? Thanks in advance.
Trying to get the lowest unused value of CrewID from the below table in a efficient manner. I cant change the table structure as its from a 3rd party and it would break our support agreement.
CREATE TABLE [dbo].[Crews](
[CrewID] [nvarchar](10) NOT NULL,
[CrewName] [nvarchar](200) NOT NULL,
[CompanyID] [nvarchar](10) NOT NULL,
[StartTime] [nvarchar](50) NULL,
[Code] .....
Ok I have upgraded my works database from a poorly designed Access database to a SQL database. The previous system allowed NULL values and duplicates to be inserted into a field that should NOT ALLOW NULL Values or duplicates. Therefore, this issue has now been moved across to my new system as I cannot set these constraints on the field that has multiple NULL values.
My solution would be to use a sequential operator, so whatever = NULL would be changed to a sequential number that us as administrators would know was a bogus number starting at something like = 999999900 counting up from that. There are only 250 records that would require updating.
To make things more interesting this field is not a integer type, its a Nvarchar type as its a Hardware ID. Both numerical and characters are require.
Hi,
As you can see, I'm totally new at Sql Server.
I have a problem, I store text into a nvarchar field (could be a 200 or 20,000 characters long string), inside the text there are several carriage returns which I would like to preserve to later presentation, but when retreiving the data from sql server I got the "cr" as "?", also I opened the database from Sql Managment and all cr's were saved as "?".
What can I do to preserve the cr inside each field ?
Thanks in advance.
Does ORDER BY work on character data type in SQL Server through ODBC?
I tried using the SQL Query Tool in SQL Enterprise Manager and it works but using through ODBC I can't get any results.
Query: SELECT company_id, company_name FROM lt_company ORDER BY company_name
company_id = integer
company_name = 30 characters
Dear all,In SQL Server 2000 , how to get distinct records sort by onefield .ExampleSELECT DISTINCT A FROM tblTEST ORBER BY BHere, In TableField 'A' contain more than one same data...Field 'B' contain all are different Data......I want distince in Field 'A' and order by Field 'B'..... how to getit.........regardskrishnan
View 2 Replies View RelatedHow can I "Order By" the second + third characters of a 7 char field ?
Sample data looks like:
LCA - L
LCB - L
LCF - M
LCE - M
LCE - A
LCA - A
LCB - A
If I order by the whole field I get:
LCA - A
LCA - L
LCB - A
LCB - L
LCE - A
LCE - M
LCF - L
LCF - M
What I want is:
LCA - L
LCB - L
LCF - L
LCA - A
LCB - A
LCE - A
LCE - M
LCF - M
I'm still at the stage in this project were I can 'split' the field (if I have to) into first 3 and last 1. But the sort order of the last 1 is not alphabetic (I want 'L', 'A', "M").
Can I substitute a custom SortOrder some way ? (I've done that with mainframe Cobol).
All suggestions appreciated.
Thanks
Roger
Hi group,
I've got a table with two columns named [Year] and [Month]. They are both defined as VarChar.
Question:
Is it possible to ORDER THEM as if they where of type DateTime?
EG
select [year], [month]
from tbl_WeightedAverageGenerated
where [Year] = 2006
ORDER BY [Month]
Returns:
2006, 10
2006, 11
2006, 12
2006, 5
2006, 6
etc...
I need it to return:
2006 5
2006 6
2006 7
2006 8
2006 9
2006 10
2006 11
2006 12
Is this possible....and how??
TIA
Regards,
SDerix
Hi,
I have small question regarding ORDER BY clause.
I wanted some value of the fields should come first before other values….Something like:
+----------+----------------------------------------+
| code | name |
+----------+----------------------------------------+
| UK | United Kingdom |
| US | United States |
| AF | Afghanistan |
| AL | Albania |
| DZ | Algeria |
| AS | American Samoa |
I want to display country UK and US code first and then rest of the countries using SQL statement. I know in MySQL, I can use ORDER BY code IN (...)
--SQL
SELECT * FROM countries ORDER by code IN ('UK', 'US') desc
But not sure how can I do same thing in SQL Server. Is there any equivalent of ORDER BY [field] IN (…) clause in SQL Server???
Though I can create a new field something like sequence and use it to display these countries but I can not do that…
Please advice.
Thanks,
Firoz Ansari
Hi, I've created a website usiing asp.net and all the data are stored in sql front. All the item are sorted in ascending order except one record. The correct order should be MP61, MP100, MP200, but this record is retrieved as MP100, MP200, MP61. If the coding is wrong, all the order displayed is not in ascending order. We have hundreds of items, but why it happens to this particular record? Can anyone help? Thanks in advance
View 3 Replies View RelatedHi,
How can I rearrange the fields in a table in SQL 7? If it is possible, will the existing data be lost?
Thank you for any help.
Newbie question. Can someone show me an SQL statement that sorts the results of a query by a field in a different table?
CREATE TABLE `Table1`
(
`table1_id` INTEGER AUTO_INCREMENT ,
`table1_name` VARCHAR(255),
PRIMARY KEY (`table1_id`)
)
CREATE TABLE `Table2`
(
`table2_id` INTEGER AUTO_INCREMENT ,
`table2_name` VARCHAR(255),
`table2_table1` INTEGER,
PRIMARY KEY (`table2_id`)
)
ALTER TABLE `Table2` ADD FOREIGN KEY (`table2_table1`) REFERENCES `Table1`(`table1_id`);
What I'd like is something like:
SELECT * FROM Table2 ORDER BY "Table1(table2_table1).tabel1_name",table2_name
It's the section in double quotes that I can't figure out how to compose.
As an example, if Table 1 has
1, A
2, B
3, C
and Table 2 has
1, a, 2
2, b, 1
3, c, 2
then I'd like the sort to return
2, b, 1
1, a, 2
3, c, 2
TIA,
Stephen
Hi AllI am finding unexpected results when inserted into a newly createdtable that has a field of datatype int identity (1,1).Basically the order I sort on when inserting into the table is notreflected in the order of the values from the identity field.Have I been wrong in assuming that it should reflect the order from thesort?The code is ...create table tmp (A varchar(50), L float, C int identity(1,1))insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Lland I don't understand why the values in tmp.C aren't in the ordersuggested by the sort.Any comments most appreciatedBevan
View 13 Replies View RelatedHi Guys,
Does anyone knows what is the evaluation order for expressions attached to "Value" property and "BackgroundColor" property for a field.
My problem is if a field is being changed in its value property to a particular value, will this changed value be recognised immediately by the Backgroundcolor property during its rule evaluation, it does not seem to be the case.
Hi there
We have a web application (database) that uses one field called Application and another called TicketType.
When a user fills out a ticket they can choose up to 3 levels of this field.
Eg Application, Application2, Application3
Eg TicketType, TicketType2, TicketType3
The extra two levels not being compulsory.
I am using sql server 2005 // Reporting Services
My query is as below:
SELECT Ticket.TicketNumber, Ticket.CreatedDate, Application_2.ApplicationName AS Application, Application_1.ApplicationName AS [App 2],
Application.ApplicationName AS [App 3], TicketType_2.TicketTypeName AS Tickettype, TicketType_1.TicketTypeName AS [Type 2],
TicketType.TicketTypeName AS [Type 3], Ticket.Description, Company.CompanyName
FROM Ticket INNER JOIN
TicketType AS TicketType ON Ticket.TicketTypeID = TicketType.TicketTypeID LEFT OUTER JOIN
TicketType AS TicketType_1 ON TicketType.ParentTicketTypeID = TicketType_1.TicketTypeID LEFT OUTER JOIN
TicketType AS TicketType_2 ON TicketType_1.ParentTicketTypeID = TicketType_2.TicketTypeID INNER JOIN
Application AS Application ON Ticket.ApplicationID = Application.ApplicationID INNER JOIN
Company ON Application.CompanyID = Company.CompanyID FULL OUTER JOIN
Application AS Application_1 ON Application.ParentApplicationID = Application_1.ApplicationID FULL OUTER JOIN
Application AS Application_2 ON Application_1.ParentApplicationID = Application_2.ApplicationID
WHERE (Ticket.CreatedDate >= @StartDate)
ORDER BY Ticket.TicketNumber
End result looks like this:
Application
App 2
App 3
TicketType
Type 2
Type 3
Software
Internal Apps
proACT
SW Other
Office Issues
General
Application
Click Track server
Alert (App)
Service
Network
Other
Network Fault
Software
Internal Apps
Other
User Account
New
Hardware
Network
HW Fault
Application
Click Track server
Alert (App)
Disk space
Office Issues
General
proACT
Configuration
Deployment
Software
Server Software
SharePoint
SW Fault
App Failure (Function)
Software
Server Software
SharePoint
SW Fault
App Failure (Function)
Ultimately I would like the Application (TicketType) fields to have the Master Information in it and the other two fields populated in order as well.
Can someone help please.
Please ask if I haven't explained myself.
thanks
Dianne
I want to store a small cirle in a text field. Can anyone tell me how I can enter it in ascii code.
Thanks
I am creating an app that allows the user to change the order of the list by changing a value in a displayOrder field. I'd love a button for move up /move down move bottom/move top and then pass that parameter to a stored procedure and it would renumber all the items in the list.
Example
ItemID description DisplayOrder Action0 item 1 0 Moveup/move down1 item 2 1 Moveup/move down2 item 3 2 Moveup/move down
So clicking on move up on item 2 would pass and itemID, Action and perhaps a list id to a stored proc and it would renumber the list. I'm assuming it would be done with a loop but I've never tried that.. suggestions?
Thanks - Mark
I am trying to order by the field and direction as provided by input parameters @COLTOSORTBY and @DIR while using a CTE and assigning Row_Number, but am running into syntax errors.
Say I have a table called myTable with columns col1,col2,col3,
Here's what I'm trying to do
with myCTE AS
(
Select
col1
,col2
,col3
,row_number() over (order by
case when(@DIR = 'ASC') then
case when @COLTOSORTBY='col1' then col1 asc
when @COLTOSORTBY='col2' then col2 asc
else col3 asc
end
else
case when @COLTOSORTBY='col1' then col1 desc
when @COLTOSORTBY='col2' then col2 desc
else col3 desc
end
end
from myTable
)
Please let me know what i can do with minimal code repetition and achive my goal of dynamically sorting column and direction. I do not want to use dynamic SQL under any circumstance.
Thanks.