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.
Author |
Topic |
akpaga
Constraint Violating Yak Guru
331 Posts |
Posted - 2013-03-08 : 14:44:47
|
Hi friendsI have a nvarchar column called customercode with following data1122810910I want to order it for my select statement and used the following sql statement :Select top 200 Customercode from Customerwhere Customercode is not nullorder by case IsNumeric(Customercode) when 1 then Replicate('0', 100 - Len(Customercode)) + CustomerCode else Customercodeend It orders fine but it does not allow to choose distinct values ..How can i achieve it..Thank you |
|
akpaga
Constraint Violating Yak Guru
331 Posts |
Posted - 2013-03-08 : 14:53:15
|
Thank You all but i resolved my issue..quote: Originally posted by akpaga Hi friendsI have a nvarchar column called customercode with following data1122810910I want to order it for my select statement and used the following sql statement :Select top 200 Customercode from Customerwhere Customercode is not nullorder by case IsNumeric(Customercode) when 1 then Replicate('0', 100 - Len(Customercode)) + CustomerCode else Customercodeend It orders fine but it does not allow to choose distinct values ..How can i achieve it..Thank you
|
|
|
djj55
Constraint Violating Yak Guru
352 Posts |
Posted - 2013-03-08 : 14:57:10
|
If I understand, what aboutSelect top 200 Customercode case when IsNumeric(Customercode) = 1 then Replicate('0', 100 - Len(Customercode)) + CustomerCodeelse Customercode end AS Customercodefrom Customerwhere Customercode is not null case when IsNumeric(Customercode) = 1 then Replicate('0', 100 - Len(Customercode)) + CustomerCode else Customercode end = 'myvalue'order by 1 djj |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-09 : 01:13:12
|
Just putting ISNUMERIC(CustomerCode) = 1 will not ensure it will contain only numeric data. ISNUMERIC returns 1 for some specific characters tooCheck the belowSELECT ISNUMERIC(val)FROM(SELECT '$' AS val UNION ALLSELECT '12e34' UNION ALLSELECT '2d4' UNION ALLSELECT ',' UNION ALLSELECT '.')t ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|