0

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.

When searching I found out this could be an ascii value, so i run this

ascii(substring(image_data, 18,1))

which returned 27

How would go about splitting this field into separate fields based on this ascii value?

Thanks in advance, Chris

EDIT: 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

The ascii values are where the spaces are in the above example of the field data

Example image of the current data

Example of the output im trying to get

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

Example HERE

Chris Wright
  • 17
  • 5

1 Answers1

0

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:

(And there is hint 3 :-) : Next time please tag with the RDBMS including the version. This makes it easier to help you. Less guessing...)

Try it like this

--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;

--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;

The result

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

The idea in short:

Better than STRING_SPLIT() is a JSON-hack, as the first is not position safe.

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.

The APPLY will search for the position of the =.

The SELECT will use the position to read the parts left and rigth from the =.

The result is a classical EAV-list.

UPDATE: If you use a version below v2016...

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;

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);

Or this:

SELECT CAST('<x name="' + REPLACE(REPLACE(@YourString,'=','">'),CHAR(27),'</x><x name="') + '</x>' AS XML);

The result is this

<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>

Or this:

<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>

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);

The result

ABS_ID  PERSON_ID   PARTY_ID    ABS_D   ABS_T   ABS_TYPE_ID ABS_ED
1234567 1234567     NULL        123     NULL    12345       123456
Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • Thank you so much for taking your time to help me. I cannot control the way the data is stored, this database comes with the software we use. However, the audit functionality in the software is not the best so im trying to build something myself. – Chris Wright Nov 14 '19 at 13:11
  • Just test UPDATE 3 - absolutely perfect. Thank you so much! – Chris Wright Nov 14 '19 at 13:18
  • When implementing this, i have realized the field names ABS_ID, PERSON_ID, PARTY_ID are not always the same. Do you know how i would tackle this? – Chris Wright Nov 14 '19 at 13:39
  • @ChrisWright You can add all known names. Missing columns will just return a NULL. More generic was an EAV-list, which is closer to the other approaches. – Shnugo Nov 14 '19 at 14:17