Search code examples
mysqlcasetrim

How can i trim leading zero based on the length of the string in mysql

I have a column named UPC in my table. That column allows max 13 digits.For example the column values should be like this.

0892902000161
0097512135764
4046228003979

Now i need to trim only one zero if it is having leading two zeros like this 0097512135764. Now i am using below query to trim all leading zeros.

SELECT TRIM(LEADING '0' FROM `UPC`) upc FROM `mytable`.

But now i need to trim only one zero if it is having two leading zeros.Some one could guide me.Any help would be greatly appreciated.

SELECT 
  CASE WHEN UPC LIKE '00%' THEN RIGHT(UPC, LENGTH(UPC) -1)
  ELSE UPC
  END AS UPC
FROM `mytable`

in the same query how can we check the count of upc number? I mean if length of upc is 13 
digits and first one is zero then need to remove first zero. Then upc length should be 12. 
If upc length is 13 and first two are zeros then need to remove single zero. 
Then also upc length should be 12 digits.How can we acheive this?
Solution

  • CASE should be sufficient. For UPC that starts with 00... get UPC without first character.

    SELECT 
  CASE WHEN UPC LIKE '00%' THEN RIGHT(UPC, LENGTH(UPC) -1)
  ELSE UPC
  END AS UPC
FROM `mytable`

    LiveDemo

    EDIT:

    If upc length is 13 and first two are zeros then need to remove single zero.

    If length of upc is 13 digits and first one is zero then need to remove first zero.

    SELECT 
  CASE 
   WHEN UPC LIKE '00%' AND LENGTH(UPC) = 13 THEN RIGHT(UPC, LENGTH(UPC) -1)
   WHEN UPC LIKE '0%' AND LENGTH(UPC) = 13 THEN RIGHT(UPC, LENGTH(UPC) -1)
  ELSE UPC
  END AS UPC
FROM `mytable`

    LiveDemo