英文:
Split a dynamic string as levels
问题
我有一个包含动态字符串的表格，其长度根据以下示例更改
字符串1：A.B.C:D.E.F:G.H.I:J字符串2：A.B.C:D
我需要拆分字符串，得到以下设计：
level_0level_1level_2level_Nlevel_Attribute
例如，对于字符串2，设计将为
level_0: Alevel_1: Blevel_2: Clevel_Attribute: D
我能够使用以下代码拆分并创建列，并将每个值分配给一个级别
DECLARE @str VARCHAR(100)='A.B.C:D.E.F:G.H.I:J';set @str=REPLACE(@str,':','.')DECLARE @JsonArray NVARCHAR(MAX)=CONCAT('[["',REPLACE(@str,'.','","'),'"]]');SELECT @str AS TheOriginal,@JsonArray AS TransformedToJSON,ValuesFromTheArray.*FROM OPENJSON(@JsonArray)WITH(Level_0 VARCHAR(100) '$[0]',Level_1 VARCHAR(100) '$[1]',Level_2 VARCHAR(100) '$[2]',Level_3 VARCHAR(100) '$[3]',Level_4 VARCHAR(100) '$[4]',Level_5 VARCHAR(100) '$[5]',Level_6 VARCHAR(100) '$[6]',Level_7 VARCHAR(100) '$[7]',Level_8 VARCHAR(100) '$[8]',Level_Attribute VARCHAR(100) '$[9]') ValuesFromTheArray
我的问题是当字符串较短时，最后一项未添加到level_attribute。
英文:
I have table containing dynamics string that changes in length as per the example below
string 1: A.B.C:D.E.F:G.H.I:Jstring 2: A.B.C:D
I need to split the string to get to the following design :
level_0level_1level_2level_Nlevel_Attribute
so for example for String 2 the design will be
level_0: Alevel_1: Blevel_2: Clevel_Attribute: D
I was able to split and create the colunm and assign each value to a level using the following
DECLARE @str VARCHAR(100)='A.B.C:D.E.F:G.H.I:J';set @str=REPLACE(@str,':','.')DECLARE @JsonArray NVARCHAR(MAX)=CONCAT('[["',REPLACE(@str,'.','","'),'"]]');SELECT @str AS TheOriginal,@JsonArray AS TransformedToJSON,ValuesFromTheArray.*FROM OPENJSON(@JsonArray)WITH(Level_0 VARCHAR(100) '$[0]',Level_1 VARCHAR(100) '$[1]',Level_2 VARCHAR(100) '$[2]',Level_3 VARCHAR(100) '$[3]',Level_4 VARCHAR(100) '$[4]',Level_5 VARCHAR(100) '$[5]',Level_6 VARCHAR(100) '$[6]',Level_7 VARCHAR(100) '$[7]',Level_8 VARCHAR(100) '$[8]',Level_Attribute VARCHAR(100) '$[9]') ValuesFromTheArray
My problem is when the string is short , the last item is not added to the level_attribute.
答案1
得分: 0
-- 1 - create table
创建表格
create table sample_levels
(
sample_data varchar(128)
);
-- 2 - add data
添加数据
insert into sample_levels values ('A.B.C:D.E.F:G.H.I:J');
insert into sample_levels values ('A.B.C:D');
-- get level + attribute
获取级别和属性
select
substring(backwards, 1, charindex(',', backwards) - 1) as attribute,
reverse(substring(backwards, charindex(',', backwards) + 1, len(backwards) - charindex(',', backwards) )) as levels
from
(
select
reverse(replace(replace(sample_data, '.', ','), ':', ',')) as backwards
from sample_levels
) as d
我在SQL Fiddle中创建了这个示例。这是用于运行SQL代码的链接。
英文:
There are many ways to solve this problem. I am choosing to leave the level as one big string and the attribute as another string. Lets create the table and insert data.
-- 1 - create tablecreate table sample_levels(sample_data varchar(128));-- 2 - add datainsert into sample_levels values ('A.B.C:D.E.F:G.H.I:J');insert into sample_levels values ('A.B.C:D');
The trick is to convert to comma delimited string and reverse and parse.
-- get level + attributeselectsubstring(backwards, 1, charindex(',', backwards) - 1) as attribute,reverse(substring(backwards, charindex(',', backwards) + 1, len(backwards) - charindex(',', backwards) )) as levelsfrom(selectreverse(replace(replace(sample_data, '.', ','), ':', ',')) as backwardsfrom sample_levels) as d
I did this example in SQL Fiddle. Here is the link to play with the SQL code.
http://sqlfiddle.com/#!18/d16f4/1/0
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论