order of nvarchar(50) field - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

order of nvarchar(50) field

Author

Topic

gjja
Starting Member

17 Posts

Posted - 2008-06-12 : 10:01:36

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?

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-06-12 : 10:05:00

Are the field lengths from MDB006-MD002 going to be constant?

gjja
Starting Member

17 Posts

Posted - 2008-06-12 : 10:06:28

The last 11 characters will always be constant

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-12 : 10:06:53

you dont need seperate field for that. just use
SELECT *,RIGHT(field2,11) AS OrderField FROm Table ORDER BY OrderField

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-06-12 : 10:15:04

No vishakh I think she meant
SELECT *,RIGHT(field2,11) AS OrderField FROm Table ORDER BY RIGHT(field2,11)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-12 : 10:21:52

quote:
Originally posted by ayamas

No vishakh I think she meant
SELECT *,RIGHT(field2,11) AS OrderField FROm Table ORDER BY RIGHT(field2,11)

No need of repeating RIGHT. you can use alias itself in ORDER BY

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-12 : 10:33:23

Also note that the returned value is still in nvarchar so it may not be displayed correctly
Can you post some sample data with expected result?

Madhivanan

Failing to plan is Planning to fail

gjja
Starting Member

17 Posts

Posted - 2008-06-12 : 13:28:09

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2008-06-12 : 13:52:46

[code]
I Guess this should work in that case.

Select * From yourTable Order by Right(Column_Name,11)
[/code]

Chirag

http://www.chirikworld.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources