Skip to content
Advertisement

How to Split 2 Strings and insert in to 2 columns

I have 2 strings

Declare @WhenDetails NVarchar(Max) ='07:00:0:0;1:00:1:0;6:00:1:0;10:00:1:0;'
Declare @Dosage NVarchar(Max) ='1.00;2.00;1.00;1.00'

I need to split these 2 string and insert into a table

Example at 07:00:0:0=>1.00 1:00:1:0=>2.00

Declare  @TempDosageWhenDetails Table (RowID INT IDENTITY(1,1), PatientMedicationID INT, Dosage NVARCHAR(Max),WhenDetails NVARCHAR(Max))
insert @TempDosageWhenDetails(Dosage)
select x.items
from dbo.Split('07:00:0:0;1:00:1:0;6:00:1:0;10:00:1:0;', ';') x

I have taken a table and split and inserted my when details

enter image description here

How can fill the dosage column as shown in the example?

Note I might have n number of records to split I have given these just an example.

Advertisement

Answer

Perhaps with a little JSON (assuming 2016+)

Example

Declare @WhenDetails NVarchar(Max) ='07:00:0:0;1:00:1:0;6:00:1:0;10:00:1:0;'
Declare @Dosage NVarchar(Max) ='1.00;2.00;1.00;1.00'

Select RowID = A.[Key]+1
      ,PatientID = null
      ,Dosage = B.[Value]
      ,WhenDetails = A.[Value]
 From (
        Select *
         From  OpenJSON( '["'+replace(@WhenDetails,';','","')+'"]' )
      ) A
 Join (
         Select *
         From  OpenJSON( '["'+replace(@Dosage,';','","')+'"]' )
      ) B
   on A.[Key]=B.[Key]

Returns

RowID   PatientID   Dosage  WhenDetails
1       NULL        1.00    07:00:0:0
2       NULL        2.00    1:00:1:0
3       NULL        1.00    6:00:1:0
4       NULL        1.00    10:00:1:0

If it helps with the Visualization:

We convert the strings into a JSON array, then it is a small matter to join the results based on the KEY

If you were to

Select * From OpenJSON( '["'+replace(@WhenDetails,';','","')+'"]' )

The results would be

key value       type
0   07:00:0:0   1
1   1:00:1:0    1
2   6:00:1:0    1
3   10:00:1:0   1

EDIT – XML Approach

Select RowID = A.RetSeq
      ,PatientID = null
      ,Dosage = B.RetVal
      ,WhenDetails = A.RetVal
 From (
        Select RetSeq = row_number() over (order by 1/0)
              ,RetVal = ltrim(rtrim(B.i.value('(./text())[1]', 'varchar(max)')))
        From  (Select x = Cast('<x>' + replace((Select replace(@WhenDetails,';','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
        Cross Apply x.nodes('x') AS B(i)
      ) A
 Join (
        Select RetSeq = row_number() over (order by 1/0)
              ,RetVal = ltrim(rtrim(B.i.value('(./text())[1]', 'varchar(max)')))
        From  (Select x = Cast('<x>' + replace((Select replace(@Dosage,';','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
        Cross Apply x.nodes('x') AS B(i)
      ) B
   on A.RetSeq=B.RetSeq
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement