New to SQL and looking for some help
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)
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
Appreciate any help getting this bit working - I can then look at the rest.