Translate to your Language

Tuesday, December 10, 2013

Netezza ISNUMERIC Data Check Logic

by Unknown  |  in DB at  2:33 AM

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

3 comments:

  1. AnonymousMay 14, 2014 at 12:32 AM

    Thanks! exactly what i was looking for. Works perfect.

    ReplyDelete
  2. AnonymousJune 16, 2014 at 8:39 AM

    Flipping Dinix's function on it's head we can remove the numbers instead and then count what's left.

    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

    This way you do not need to worry about which special characters to include.

    ReplyDelete
  3. UnknownJune 17, 2014 at 2:34 AM

    This is good one, I will update my post with it

    ReplyDelete

© Copyright © 2015Big Data - DW & BI. by