Unpicking a string of data

Transact-SQL
1

New to SQL and looking for some help :smile:

I need to unpick some audit data which an application appends onto the same row each time the application is used.

The raw data is as follows;

Field 1- 1661638|

Field 2 - 15045ý15045ý15046ý15541ý15624ý15624ý15624|

Field 3 - 78959ý79101ý29012ý50278ý68742ý68742ý68782|

Field 4 - JANJONýJANJONýCHRJONýGRABISýJANBISýJANBISýJANBIS|

Field 5 - nprýALXýsecýADDýDOBýSEXýDOB|

Field 6 - MOUSE Mickey 01/01/1922 Female (Req CA9002662G)ý(Req CA9002663S)ý3000 to ý(Req CA9200177R)ý01/01/1922 (Req CA2853971B)ýFemale (Req CA2853971B)ý01/01/1948 (Req CA2853971B)|PP1661643

The above example (not actual data) has 7 updates (primary identifier is Field 1 which does not get updated) with the ý being the delimiter. My plan is to create a tmp table with ý + 1 rows and then dump out somehow (prob into excel).

I think I'm fairly close but am struggling where the data size changes in field 6 - I thought I had it working but a bit of testing proves not.

My attempt at code so to unpick Field 6 so far is as follows;

Declare @loop int, @pos int = 0, @i int = 0 -- declare variables
Set @loop = (SELECT LEN(Time) - LEN(REPLACE(Time, 'ý', '')) from [dbo].[Audit_Source_Data]) + 1 --how many 'ý' are there? - need that number + 1 rows in new table
while (@i < @loop)
Begin
Select @pos as pos
SELECT SUBSTRING (AddInfo, @pos+1, (SELECT CHARINDEX('ý', AddInfo) from [dbo].[Audit_Source_Data])-1) from [dbo].[Audit_Source_Data] -- captures text between next ý positions
set @pos = (SELECT CHARINDEX('ý', AddInfo, @pos+1) from [dbo].[Audit_Source_Data]) -- updates next position of pos
set @i = @i + 1
End

Appreciate any help getting this bit working - I can then look at the rest.

C.

2

Just to say Field 2 is time and Field 6 is AddInfo :slight_smile:

3

Your description is very vague, so I don't know if the input is separate rows in one table, or separate columns in one row. If there is a field#/name or not.

But it looks like you might just need a standard table-based function. DelimitedSplit8K is an excellent one (you can Google to get the source for it).

For example, here's how to use it to split the strings into separate rows:

CREATE TABLE #fields 
(
    field# int NOT NULL,
    audit_data nvarchar(4000) NULL
)
INSERT INTO #fields VALUES
(1,N'1661638|'),
(2,N'15045ý15045ý15046ý15541ý15624ý15624ý15624|'),
(3,N'78959ý79101ý29012ý50278ý68742ý68742ý68782|'),
(4,N'JANJONýJANJONýCHRJONýGRABISýJANBISýJANBISýJANBIS|'),
(5,N'nprýALXýsecýADDýDOBýSEXýDOB|'),
(6,N'MOUSE Mickey 01/01/1922 Female (Req CA9002662G)ý(Req CA9002663S)ý3000 to ý(Req CA9200177R)ý01/01/1922 (Req CA2853971B)ýFemale (Req CA2853971B)ý01/01/1948 (Req CA2853971B)|PP1661643')

SELECT ds.Item
FROM #fields f
CROSS APPLY dbo.DelimitedSplit8K(f.audit_data, N'ý') ds
ORDER BY f.field#, ds.ItemNumber