on 02-13-2018 10:24 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.