Sql将分隔的字符串分割成列 [英] Sql split delimited string into columns
问题描述
我尝试了以下内容,我可以通过_分割第3个字符串,但是当我尝试'FBC_asda_CB_All_Quota_A01'时它不起作用。
有人可以帮我拆分'FBC_asda_CB_All_Quota_A01'
提前致谢。
我尝试了什么:
,t为(选择'FBC_abc_CB'为val)
选择t。*,
LEFT(val,charindex('_',val) - 1),
SUBSTRING(val,charindex('_',val)+ 1,
len(val) - CHARINDEX('_',reverse(val)) - charindex('_',val)),
REVERSE(LEFT(反转) (val),charindex('_',reverse(val)) - 1))
来自t;
Hi,
I Tried the below and i can get the frist 3 string split by _, but when i tried to 'FBC_asda_CB_All_Quota_A01' its not working.
can someone help me to split the 'FBC_asda_CB_All_Quota_A01'
Thanks in advance.
What I have tried:
with t as (select 'FBC_abc_CB' as val)
select t.*,
LEFT(val, charindex('_', val) - 1),
SUBSTRING(val, charindex('_', val)+1,
len(val) - CHARINDEX('_', reverse(val)) - charindex('_', val)),
REVERSE(LEFT(reverse(val), charindex('_', reverse(val)) - 1))
from t;
推荐答案
简单分裂isn很难,只是凌乱,因为SQL字符串处理最好是穷人:转换逗号分隔的数据a列中的行以供选择 [ ^ ]
用下划线代替逗号应该这样做。
Simple splitting isn't difficult, just messy because SQL string handling is at best poor: Converting comma separated data in a column to rows for selection[^]
Replacing commas with underscores should do it.
这篇关于Sql将分隔的字符串分割成列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!