InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 21:53:07 GMT20http://www.sqlservercentral.com/Forums/Topic728384-338-1.aspxI suggested that as an alternative. I assume that in his case, he wants to know the value that doesn't have a dash in it. But, if that's the case, it's probably more efficient to just return the the string to the right of the dash WITHOUT converting it to an integer.And then there's the question of ordering in this sequence 1,2,11 (integer) or 1,11,2 (char). Fun, fun, fun. :-)Fri, 05 Jun 2009 08:44:31 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic728384-338-1.aspx[quote][b]ramadesai108 (6/3/2009)[/b][hr]Hi Bob, Converting the results to VarChar did fix it. Thanks for your help.[/quote]Now you are doing a lot of extra work. After your IsNumeric check, just return the value instead of converting it to an int. When the IsNumeric returns 0, and you are returning a zero, return a '0' instead.Wed, 03 Jun 2009 18:17:51 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic728384-338-1.aspxHi Bob, Converting the results to VarChar did fix it. Thanks for your help.Wed, 03 Jun 2009 13:24:19 GMTramadesai108http://www.sqlservercentral.com/Forums/Topic728384-338-1.aspxThe ORDER BY wants all values to be of the same datatype. Where you find a '-' in the string for ssid, such that a numeric value is available, you are returning that numeric value as an integer. Where you don't find a '-', you are returning the entire string 'ABC26'. You either need to CONVERT the integer results to varchar, or arbitrarily return a 0 or null instead of ssid, where there is no '-##' in the string.Wed, 03 Jun 2009 13:12:28 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic728384-338-1.aspxWhat is wrong?Wed, 03 Jun 2009 12:56:21 GMTramadesai108http://www.sqlservercentral.com/Forums/Topic728384-338-1.aspxHi All, I am running the following SQL statement with Case statement in Order by clause:SELECT ssID FROM TblAORDER BY CASE WHEN CHARINDEX('-',ssID) = 0 THEN --there are no dashes ssID ELSE 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 ssID END ENDHere is the data:AB26-1AB26-2AB26-3AB26-4AB26-5AB26Running the query error out with:Conversion failed when converting the nvarchar value 'AB26' to data type intIt 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?Wed, 03 Jun 2009 10:59:39 GMTramadesai108