Skip to Content
avatar image
-1
Former Member

Removing leading Zero's from VARCHAR field - only for numbers

Hi,

We have a scenario where we need to trim the leading zeros only for Materials which has numbers and it should be retained for Materials which are Alpha numeric.

000000000000012345 - Should be changed to 12345

001-AA-5TEST - No change needed for this.

Is there a function to check if a value in a column is Alpha numeric ?

Please suggest.

Thanks,

Gokul

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Feb 14 at 12:30 AM

    So after posting that I thought about getting the results I think you wanted, all the data with just the digit values trimmed, you can try a query something like this:

    SELECT A, B FROM T where B LIKE_REGEXPR '\D'
    UNION
    SELECT A, LTRIM(B,'0') FROM T where NOT B LIKE_REGEXPR '\D'
    ORDER BY A;
    

    Assuming there is something to put them back in an order you would prefer. But the end results look like this:

    A    B
    1    12345
    2    001-AA-5TEST
    
    Add comment
    10|10000 characters needed characters exceeded

  • May 02 at 08:08 AM
    -1

    Hello,

    You can also use the below formula which was really fast.

    Formula 1:

    if( match("PRODUCT_ID", '*A*') OR match("PRODUCT_ID", '*B*') OR match("PRODUCT_ID", '*C*') OR match("PRODUCT_ID", '*D*') OR match("PRODUCT_ID", '*E*') OR match("PRODUCT_ID", '*F*') OR match("PRODUCT_ID", '*G*') OR match("PRODUCT_ID", '*H*') OR match("PRODUCT_ID", '*I*') OR match("PRODUCT_ID", '*J*') OR match("PRODUCT_ID", '*K*') OR match("PRODUCT_ID", '*L*') OR match("PRODUCT_ID", '*M*') OR match("PRODUCT_ID", '*N*') OR match("PRODUCT_ID", '*O*') OR match("PRODUCT_ID", '*P*') OR match("PRODUCT_ID", '*Q*') OR match("PRODUCT_ID", '*R*') OR match("PRODUCT_ID", '*S*') OR match("PRODUCT_ID", '*T*') OR match("PRODUCT_ID", '*U*') OR match("PRODUCT_ID", '*V*') OR match("PRODUCT_ID", '*W*') OR match("PRODUCT_ID", '*X*') OR match("PRODUCT_ID", '*Y*') OR match("PRODUCT_ID", '*Z*') , "PRODUCT_ID",ltrim("PRODUCT_ID",'0'))

    Formula 2:

    if(

    strlen( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace("PRODUCT_ID",'0','') ,'1', '') ,'2', '') ,'3', '') ,'4', '') ,'5', '') ,'6', '') ,'7', '') ,'8', '') ,'9', '') ) > 0 ,1,0)

    Thanks
    RufusSamuel

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 14 at 12:20 AM

    Not sure if this is enough or not, but you can use LIKE_REGEXPR in the where clause to identify those with non digits in the values and maybe then do something with them.

    CREATE TABLE T (A INT, B varchar(50));
    INSERT INTO T VALUES (1, '000000000000012345');
    INSERT INTO T VALUES (2, '001-AA-5TEST');
    
    SELECT A, B FROM T where B LIKE_REGEXPR '\D';
    
    -- or reverse it like this with the NOT operator
    
    SELECT A, B FROM T where NOT B LIKE_REGEXPR '\D';
    

    The first query returns just the second row because it has something other than digits in it. While the second query returns just the first row because it is all digits.

    Hope that helps,

    Chris

    Add comment
    10|10000 characters needed characters exceeded