Sorting Nvarchar values where some values are numeric

Question

I am using SQL Server 2008 R2 and I am trying to sort some nvarchar values that also include numeric values in order. I developed this test Case statement which I want to include in the order by statement in a similar form but it is not working:

 Case WHEN [MyValue] like '[0-9]%' THEN 'Yes' ELSE 'No' END as isReallyNumeric

Strange thing is when I encounter this nvarchar value 10810001;G it evalutes as numeric. Also I tried the Transact SQL function isNumeric and that was not any help either. Perhaps I am missing something obvious but I've can't seem to find a solution no matter what I do. Any solutions would be greatly appreciated!

---

Petey100

Answer 1

try something like this,

DECLARE @table TABLE (Value varchar(50))
INSERT INTO @table
SELECT '10810001;G' UNION
SELECT '108101' UNION
SELECT '1001G' UNION
SELECT '1000' UNION
SELECT '1081G'

;SELECT
Value,CASE
WHEN ISNUMERIC(value)=1
THEN
    (CASE WHEN 
(CHARINDEX('D', Value) <> 0) OR 
(CHARINDEX('E', Value) <> 0) OR 
(CHARINDEX('$', Value) <> 0)
         THEN 'no'
         ELSE 'yes' 
    END)
ELSE 'no'
END AS isreallynumeric
FROM @table
	
	

---

Regards, Dineshkumar,
Please 'Mark as Answer' if my post answers your question and 'Vote as Helpful' if it helps you

Answer 2

If by "numeric" in this case you mean the string only contains the digits 0-9 and nothing else, then the test would be

Case WHEN [MyValue]  like '%[^0-9]%' THEN 'No' ELSE 'Yes' END as isReallyNumeric

That is you search for is there a character in the string which is not a 0-9.  Also note that the above will count the empty string '' as numeric and count NULL as numeric.  If you don't want that then do

Select *, Case WHEN [MyValue]  like '%[^0-9]%' Or IsNull(Myvalue, '') = '' THEN 'No' ELSE 'Yes' END as isReallyNumeric

Tom

Answer 3

That works thanks! FYI, I want to incorporate that into an order by statement on a column of nvarchar values which has a mix of numerics and alphanumeric values. Seems there are expression short circuiting issues with CASE statements. I hit this when evaluating that it is numeric and try to convert it to a BIGINT. For now I am sorting by len[myvalue], myvalue as a poor man's sort to avoid potential issues. However that's a great solution for one part of the problem!!!!

---

Petey100