Skip to Content
-1

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

Feb 13 at 10:24 PM

238

avatar image
Former Member

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

10 |10000 characters needed characters left characters exceeded

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!

1
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Christopher Luttrell Feb 14 at 12:30 AM
1

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
Share
10 |10000 characters needed characters left characters exceeded
RUFUS SAMUEL 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

Share
10 |10000 characters needed characters left characters exceeded
Christopher Luttrell Feb 14 at 12:20 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded