| Author |
 Topic  |
|
|
isi_rajah19
Starting Member
2 Posts |
 Posted - 07/27/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/27/2012 : 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
USA
189 Posts |
Posted - 07/27/2012 : 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 - 07/27/2012 : 08:45:18
|
Thanks |
|
|
| |
Topic |
|
|
|
Order by alphanumeric
