Netezza dosen't have any isnumeric check function, so in order to find out whether the column has non-numeric, use the following logic
nvl(instr(translate(Column_Name,
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'X'),0)
if the column has non numeric then it will return 1 else it will be 0, I haven't included the special characters in the logic, if you have '%,$,£..' then include them in the translate part and it will work
One of our visitor posted the following method
istrue(length(translate(Column_Name,'0123456789',''))=0) as isnumeric
If the resulting length is zero then it is numeric, or the string was empty. If the latter is a possibility then you could add another check
istrue(length(translate(Column_Name,'0123456789',''))=0 and length(Column_Name)>0) as isnumeric
nvl(instr(translate(Column_Name,
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'X'),0)
if the column has non numeric then it will return 1 else it will be 0, I haven't included the special characters in the logic, if you have '%,$,£..' then include them in the translate part and it will work
One of our visitor posted the following method
istrue(length(translate(Column_Name,'0123456789',''))=0) as isnumeric
If the resulting length is zero then it is numeric, or the string was empty. If the latter is a possibility then you could add another check
istrue(length(translate(Column_Name,'0123456789',''))=0 and length(Column_Name)>0) as isnumeric
Thanks! exactly what i was looking for. Works perfect.
ReplyDeleteFlipping Dinix's function on it's head we can remove the numbers instead and then count what's left.
ReplyDeleteistrue(length(translate(Column_Name,'0123456789',''))=0) as isnumeric
If the resulting length is zero then it is numeric, or the string was empty. If the latter is a possibility then you could add another check
istrue(length(translate(Column_Name,'0123456789',''))=0 and length(Column_Name)>0) as isnumeric
This way you do not need to worry about which special characters to include.
This is good one, I will update my post with it
ReplyDelete