Posts
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
ReplyDeleteHow about using one of the Regular Expression functions as follows:
ReplyDeleteWHERE NOT REGEXP_LIKE(FIELDTOCHK,'[0-9]')
Or wrapping it into a case statement:
CASE WHEN NOT REGEXP_LIKE(FIELDTOCHK,'[0-9]') THEN 0 ELSE 1 END AS ISNUMERIC
You may want to strip out full stops, commas, currency signs, etc. before doing this. You can use REGEXP_REPLACE to do that.
DeleteThanks. Just what I need.
ReplyDelete