根据 ascii 值拆分列 [英] Split column based on ascii value
问题描述
我在包含一个大字符串的表中有一个 text 字段，我想分隔的字符串的每个部分都被一个小方块分割.
I have a text field in a table that contains a large string, each part of the string that i want to separate is split by a little square.
搜索时我发现这可能是一个 ascii 值，所以我运行这个
When searching I found out this could be an ascii value, so i run this
ascii(substring(image_data, 18,1))
返回27
如何根据这个 ascii 值将此字段拆分为单独的字段?
How would go about splitting this field into separate fields based on this ascii value?
提前致谢，克里斯
数据当前外观示例.如果可能的话，在 = 之前将 TEXT 作为标题会很棒.
Example of what the data currently looks like. Having the TEXT before the = as the Header would be great if it is possible.
ABS_ID=1234567 PERSON_ID=1234567 PARTY_ID= ABS_D=123 ABS_T= ABS_TYPE_ID=12345 ABS_ED=123456
ABS_ID=1234567 PERSON_ID=1234567 PARTY_ID= ABS_D=123 ABS_T= ABS_TYPE_ID=12345 ABS_ED=123456
ascii 值是上面字段数据示例中空格所在的位置
The ascii values are where the spaces are in the above example of the field data
更新下面提供的代码非常适合我最初给出的示例.在实现它时，我发现审计字符串根据 ENTITY_NM
UPDATE The code provided below works great for the example i initially given. When implementing it i discovered the Audit string is different depending on the ENTITY_NM
推荐答案
提示:您可能需要先阅读更新 3" :-)
Hint: You might want to read "update 3" first :-)
还有最重要的提示:不要以这种格式存储数据.如果你能改变这一点，你真的应该......
And the most important hint: Do not store data in such a format. If you can change this, you really should...
现在有一些不同的方法:
Now some different approaches:
(还有提示 3 :-) :下次请用 RDBMS 进行标记，包括版本.这样可以更轻松地帮助您.少猜...)
(And there is hint 3 :-) : Next time please tag with the RDBMS including the version. This makes it easier to help you. Less guessing...)
试试这个
--首先，我通过用 27 替换分隔空格来模拟您的问题.现在我们看到了您所说的矩形.
--First I mock-up your issue by replacing the separating blanks with the 27. Now we see the rectangles you were talking about.
DECLARE @YourString VARCHAR(1000)=REPLACE('ABS_ID=1234567 PERSON_ID=1234567 PARTY_ID= ABS_D=123 ABS_T= ABS_TYPE_ID=12345 ABS_ED=123456',' ',CHAR(27));
SELECT @YourString;
--这是查询(需要 v2016+):
--This is the query (needs v2016+):
SELECT A.[key] AS Position
,LEFT(Fragment,PosEqual-1) AS ValueName
,SUBSTRING(Fragment,PosEqual+1,1000) AS ValueContent
FROM OPENJSON(CONCAT('["',REPLACE(@YourString,CHAR(27),'","'),'"]')) A
CROSS APPLY(SELECT A.[value] AS Fragment
,CHARINDEX('=',A.[value]) AS PosEqual) B;
结果
Position ValueName ValueContent
0 ABS_ID 1234567
1 PERSON_ID 1234567
2 PARTY_ID
3 ABS_D 123
4 ABS_T
5 ABS_TYPE_ID 12345
6 ABS_ED 123456
简单的想法:
比 STRING_SPLIT() 更好的是 JSON-hack，因为第一个不是位置安全的.
Better than STRING_SPLIT() is a JSON-hack, as the first is not position safe.
使用一些简单的字符串方法，我们可以将分隔的字符串转换为 JSON 数组.我们使用 OPENJSON() 打开这个数组.此方法将位置返回为 key，将片段返回为 value.
Using some simple string methods we can transform your separated string in a JSON array. This array we open using OPENJSON(). This method returns the position as key and the fragment as value.
APPLY 将搜索 = 的位置.
SELECT 将使用位置读取 = 的 left 和 rigth 部分.
The SELECT will use the position to read the parts left and rigth from the =.
结果是一个经典的 EAV 列表.
The result is a classical EAV-list.
以下查询在原理上类似，但使用 XML-hack 并适用于 v2005 以下的版本:
the following query is similiar in principles, but uses a XML-hack and works with versions down to v2005:
SELECT LEFT(C.Fragment,C.PosEqual-1) AS ValueName
,SUBSTRING(C.Fragment,C.PosEqual+1,1000) AS ValueContent
FROM (SELECT CAST('<x>'+REPLACE(@YourString,CHAR(27),'</x><x>')+'</x>' AS XML)) A(CastedToXml)
CROSS APPLY A.CastedToXml.nodes('/x') B(xmlFragment)
CROSS APPLY(SELECT B.xmlFragment.value('text()[1]','nvarchar(1000)') AS Fragment
,CHARINDEX('=',B.xmlFragment.value('text()[1]','nvarchar(1000)')) AS PosEqual) C;
更新 2:另一种方法
您可以像这样一次性将其拆分:
UPDATE 2: One more approach
You might split this in one single go like this:
SELECT CAST('<x><y>' + REPLACE(REPLACE(@YourString,'=','</y><y>'),CHAR(27),'</y></x><x><y>') + '</y></x>' AS XML);
或者这个:
SELECT CAST('<x name="' + REPLACE(REPLACE(@YourString,'=','">'),CHAR(27),'</x><x name="') + '</x>' AS XML);
结果是这样
<x>
<y>ABS_ID</y>
<y>1234567</y>
</x>
<x>
<y>PERSON_ID</y>
<y>1234567</y>
</x>
<x>
<y>PARTY_ID</y>
<y />
</x>
<x>
<y>ABS_D</y>
<y>123</y>
</x>
<x>
<y>ABS_T</y>
<y />
</x>
<x>
<y>ABS_TYPE_ID</y>
<y>12345</y>
</x>
<x>
<y>ABS_ED</y>
<y>123456</y>
</x>
或者这个:
<x name="ABS_ID">1234567</x>
<x name="PERSON_ID">1234567</x>
<x name="PARTY_ID" />
<x name="ABS_D">123</x>
<x name="ABS_T" />
<x name="ABS_TYPE_ID">12345</x>
<x name="ABS_ED">123456</x>
更新 3(这应该是最重要的 :-) )
这将隐式地进行透视:
UPDATE 3 (this should be on top probably :-) )
This will do the pivoting implicitly:
SELECT CastedToXml.value('(/x[@name="ABS_ID"]/text())[1]','bigint') AS ABS_ID
,CastedToXml.value('(/x[@name="PERSON_ID"]/text())[1]','bigint') AS PERSON_ID
,CastedToXml.value('(/x[@name="PARTY_ID"]/text())[1]','bigint') AS PARTY_ID
,CastedToXml.value('(/x[@name="ABS_D"]/text())[1]','bigint') AS ABS_D
,CastedToXml.value('(/x[@name="ABS_T"]/text())[1]','bigint') AS ABS_T
,CastedToXml.value('(/x[@name="ABS_TYPE_ID"]/text())[1]','bigint') AS ABS_TYPE_ID
,CastedToXml.value('(/x[@name="ABS_ED"]/text())[1]','bigint') AS ABS_ED
FROM
(SELECT CAST('<x name="' + REPLACE(REPLACE(@YourString,'=','">'),CHAR(27),'</x><x name="') + '</x>' AS XML)) A(CastedToXml);
结果
ABS_ID PERSON_ID PARTY_ID ABS_D ABS_T ABS_TYPE_ID ABS_ED
1234567 1234567 NULL 123 NULL 12345 123456
这篇关于根据 ascii 值拆分列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
