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