Search code examples
mysqlsqlregexreplacetrim

Trim leading zeros in comma separated values in a column

Ex.Table Store

store_id             Employee_id
0020,0345,0002345    0234
0034                 0943

I tried REPLACE(LTRIM(REPLACE(store_id,'0',' ')),' ','0') but it trims leading zeros for the first value alone. How to get all storeIds of an employee without leading zeros in a sql query?

Is it possible?

Solution

  • If you just want to remove the leading zeros -- and there aren't too many -- you can use replace():

    select substr(replace(replace(replace(concat(',', store_id), ',0', ','),
                              ',0', ','),
                       ',0', ',')
              2, length(store_id)

    This just replaces a comma followed by a zero with a comma, adding and removing a comma at the beginning and end of the string. Different databases have slightly different names for substr() and length(), but the functionality is generally there.