在列中以逗号分隔的值修饰前导0

Ex.Table Store

Ex.Table商店

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?

我尝试了REPLACE(LTRIM(REPLACE) (store_id，'0'，')，' ' '，'0')，但它仅为第一个值修饰了前导零。如何在sql查询中不引入0的情况下获得雇员的所有存储?

Is it possible?

是可能的吗?

2 个解决方案

#1

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

如果您只是想删除前导零——而且没有太多——您可以使用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.

这只是用逗号替换一个逗号，后面加上一个逗号，在字符串的开头和结尾添加一个逗号。不同的数据库对substr()和length()的名称略有不同，但是功能通常都在那里。

#2

If this is a table in an rdbms, it violates 1NF. You should avoid doing this. If you possible use junction tables or reference tables. Else you could use the same schema and insert multiple entries corresponding to a single Employee_id.

如果这是rdbms中的一个表，那么它就违反了1NF。你应该避免这样做。如果可能的话，使用连接表或引用表。否则，您可以使用相同的模式并插入与单个Employee_id对应的多个条目。

Now, To solve this in a generic manner, there is only one solution, UDF's. User defined function specification is here

现在，要以通用的方式解决这个问题，只有一个解决方案，UDF。这里是用户定义的函数规范

or move this kind of processing to the client.

或者将这种处理转移到客户端。

#1