Order by alphanumeric - 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 2008 Forums

Transact-SQL (2008)

Order by alphanumeric

Author

Topic

isi_rajah19
Starting Member

2 Posts

Posted - 2012-07-27 : 05:16:34

Hi i am currently doing a project in which the database needs to sort the lot numbers
prefix is nvarchar
lotnum is int
suffix is nvarchar

i have managed to convert the lot number
code i used is
Select (case when prefix is null then '' else prefix end) +
CONVERT ( nvarchar , ( lotnumber ) ) +(case when suffix is null then '' else suffix end)
(values in the database are a1a,1a,1,2,100)
when i order by lotnumber i get
a1a
1a
1
2
100

then prefix to the order by
and get this result
1
a1a
1a
2
100

i have added the suffix as well and returns the same result

i need to order it as follows

1
1a
2
100
a1a

Please could someone help me on this

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-27 : 06:00:42

order by case when col like '[0-9]%' then 1 else 2 end, col

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sql-programmers
Posting Yak Master

190 Posts

Posted - 2012-07-27 : 06:17:54

CREATE TABLE #Tab
(
Prefix VARCHAR(20) ,
lotnumber INT ,
Suffix VARCHAR(20)
)

INSERT INTO #Tab
VALUES ( 'a', 1, 'a' )
INSERT INTO #Tab
VALUES ( NULL, 1, 'a' )
INSERT INTO #Tab
VALUES ( NULL, 1, NULL )
INSERT INTO #Tab
VALUES ( NULL, 2, NULL )
INSERT INTO #Tab
VALUES ( NULL, 100, NULL )

SELECT lotnumber
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY lotnumber, Prefix, Suffix ) AS RowNum ,
( CASE WHEN prefix IS NULL THEN ''
ELSE prefix
END ) + CONVERT (NVARCHAR, ( lotnumber ))
+ ( CASE WHEN suffix IS NULL THEN ''
ELSE suffix
END ) AS lotnumber ,
lotnumber AS OrderBylotnumber
FROM #Tab
) s
ORDER BY CASE WHEN lotnumber LIKE '[0-9]%' THEN 1
ELSE 2
END ,
RowNum

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

isi_rajah19
Starting Member

2 Posts

Posted - 2012-07-27 : 08:45:18

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources