cancel
Showing results for 
Search instead for 
Did you mean: 

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

gokul_jayaraman2
Explorer

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

lbreddemann
Active Contributor

Three hints on that:

1. Search before you post a question!

2. regexpr_-functions are your friend for this.

3. Search before you post a question!

Accepted Solutions (1)

Accepted Solutions (1)

former_member532835
Discoverer

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

Answers (3)

Answers (3)

itsvikaskr
Member
0 Kudos

The simplest way I found to do this is using the TRIM function with LEADING/TRAILING/BOTH extension.

Example below-

Query: select TRIM( LEADING '0' FROM '000001234') from dummy

Result: 1234

former_member462348
Participant
0 Kudos

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

former_member532835
Discoverer
0 Kudos

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