I am running the following SQL statement with Case statement in Order by clause:
CASE WHEN CHARINDEX('-',ssID) = 0 THEN --there are no dashes
CASE WHEN ISNUMERIC(SUBSTRING(ssID,CHARINDEX('-',ssID) + 1,LEN(ssID) - CHARINDEX('-',ssID))) = 1 THEN
CONVERT(INT,SUBSTRING(ssID,CHARINDEX('-',ssID) + 1,LEN(ssID) - CHARINDEX('-',ssID)))
ELSE --there are dashes but no numbers after dashes
Here is the data:
Running the query error out with:
Conversion failed when converting the nvarchar value 'AB26' to data type int
It seems to be erroring on the last line of data, "AB26", which does not have a dash.
Since it does not have a dash, it should not even go thru the 2nd case statement.
What is wrong here?