quote:

---

Originally posted by akpaga

Hi friends

I have a nvarchar column called customercode with following data
1
1
2
2
8
10
9
10
I want to order it for my select statement and used the following sql statement :
Select top 200 Customercode from
Customer
where Customercode is not null
order by case IsNumeric(Customercode)
when 1 then Replicate('0', 100 - Len(Customercode)) + CustomerCode
else Customercode
end

It orders fine but it does not allow to choose distinct values ..How can i achieve it..Thank you

---

Select top 200 Customercode 
case when IsNumeric(Customercode) = 1 then Replicate('0', 100 - Len(Customercode)) + CustomerCode
else Customercode end AS Customercode
from Customer
where Customercode is not null
    case when IsNumeric(Customercode) = 1 then Replicate('0', 100 - Len(Customercode)) + CustomerCode else Customercode end = 'myvalue'
order by 1

SELECT ISNUMERIC(val)
FROM
(
SELECT '$' AS val UNION ALL
SELECT '12e34' UNION ALL
SELECT '2d4' UNION ALL
SELECT ','  UNION ALL
SELECT '.'
)t