SQL Bugging me

[mole999]

This works
case when (isnumeric(SUBSTRING(DRL.LICENCE_NUMBER,7,2))) = 1 AND SUBSTRING(DRL.LICENCE_NUMBER,7,2) >12 then 'X' else SUBSTRING(DRL.LICENCE_NUMBER,7,2) end,

I want to change 'X' to

SUBSTRING(DRL.LICENCE_NUMBER,7,2) -50,

it throws an error as some of the values retrieved aren't numeric so tried

isnumeric(SUBSTRING(DRL.LICENCE_NUMBER,7,2))) = 1 AND SUBSTRING(convert(varchar(2),DRL.LICENCE_NUMBER),7,2)-50
or
isnumeric(SUBSTRING(DRL.LICENCE_NUMBER,7,2)) = 1 AND SUBSTRING(DRL.LICENCE_NUMBER),7,2)-50

it could BE IF something something, but I'm not seeing it, any thoughts

 

[Joe4]

Did you try nesting it?

I am guessing it doesn't like you checking to see if it is numeric and then doing a number comparison in the same statement, because if it is not numeric, I think it might still be testing the number comparison, which will bomb out.
Maybe something like the first reply here: sql - Check if field is numeric, then execute comparison on only those field in one statement? - Stack Overflow
or the structure of the nested Case statement shown here: IsNumeric, IsInt, IsNumber - Microsoft SQL Server: Programming FAQ - Tek-Tips

 

[mole999]

Thanks, i'll go do some checking

 

[mole999]

well I can't use it in the WHERE clause (testing 3000 plus) and need to have a physical value as a compare, and I can't add functions to the environment hohum

 

[mole999]

changed it slightly, didn't actually need to remove 50 just make it recognisable,

SUBSTRING(DRL.LICENCE_NUMBER,9,2)+'/'+case when isnumeric(SUBSTRING(DRL.LICENCE_NUMBER,7,1)) = 1 then replace(REPLACE(SUBSTRING(DRL.LICENCE_NUMBER,7,1),'5','0') ,'6','1') else SUBSTRING(DRL.LICENCE_NUMBER,7,1) end +''+SUBSTRING(DRL.LICENCE_NUMBER,8,1)+'/'+SUBSTRING(DRL.LICENCE_NUMBER,6,1)+''+SUBSTRING(DRL.LICENCE_NUMBER,11,1) As [DL DOBtest],