CODEWITHSUNDEEP
sql-servert-sql
john Cogdle
john Cogdle

Reputation: 1533

split data from long string text then update each data

I am trying to create a bit complex stored procedure in ms sql database. My main focus is take a input of long string text then split the data by characters and update that data where matching. In detail: DataString is the input long text string which will contain value like bellow example.
'-' is separator between name of data type and its value
',' is separator between two different kind of value
':' is separator between two set of data, each set of data separates by :
Now can u tell me how can i grab each data from long string and insert them where matching? Ask question if you still need to know something. Thanks in advance

Example of long text string:

ASIN-NsQf8,type-0,Price-7,IsPrime-1:ASIN-fD5tsQ,type-1,Price-13,IsPrime-0:ASIN-tvQtsu,type-1,Price-14,IsPrime-1

The Unfinished SQL Code:

CREATE PROCEDURE dbo.lk_UpdateMatchingDataOfThirdparty 
@DataString VARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE ThirdPartyData SET Price = @value_get_from_string, IsPrime = @value_get_from_string, DateChecked = GETDATE()
    WHERE ASIN = '@value_get_from_string' AND type = '@value_get_from_string';
END
GO

Upvotes: 0

Views: 84

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 82000

Unfortunately string_split() does NOT return a sequence number. So if you are open to a an alternative split/parse function which is also performant.

Note: I made assumptions with the try_convert() portion

Example

Declare @DataString varchar(max) = 'ASIN-NsQf8,type-0,Price-7,IsPrime-1:ASIN-fD5tsQ,type-1,Price-13,IsPrime-0:ASIN-tvQtsu,type-1,Price-14,IsPrime-1'

UPDATE A 
   SET Price       = B.Price
     , IsPrime     = B.IsPrime
     , DateChecked = GETDATE()
 From  ThirdPartyData A
 Join  (
        Select [ASIN]   =replace(max(case when B.RetVal Like 'ASIN-%' then B.RetVal end),'ASIN-','')
              ,[Type]   =try_convert(int,replace(max(case when B.RetVal Like 'type-%' then B.RetVal end),'type-',''))
              ,[Price]  =try_convert(money,replace(max(case when B.RetVal Like 'price-%' then B.RetVal end),'price-',''))
              ,[IsPrime]=try_convert(bit,replace(max(case when B.RetVal Like 'IsPrime-%' then B.RetVal end),'IsPrime-',''))
         From  [dbo].[tvf-Str-Parse](@DataString,':') A
         Cross Apply [dbo].[tvf-Str-Parse](A.RetVal,',') B
         Group By A.RetSeq
       ) B 
   on  A.[ASIN]=B.[ASIN] and A.[Type]=B.[Type]

If it Helps with the Visualization, the sub-query Returns 

ASIN    Type    Price   IsPrime
tvQtsu  1       14.00   1
NsQf8   0       7.00    1
fD5tsQ  1       13.00   0

The TVF if Interested

CREATE FUNCTION [dbo].[tvf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    Select RetSeq = Row_Number() over (Order By (Select null))
          ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From  (Select x = Cast('<x>' + replace((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i)
);

EDIT: If you want the 2016+ string_split() approach

Select [ASIN]   =replace(max(case when Value Like 'ASIN-%' then Value end),'ASIN-','')
      ,[Type]   =try_convert(int,replace(max(case when Value Like 'type-%' then Value end),'type-',''))
      ,[Price]  =try_convert(money,replace(max(case when Value Like 'price-%' then Value end),'price-',''))
      ,[IsPrime]=try_convert(bit,replace(max(case when Value Like 'IsPrime-%' then Value end),'IsPrime-',''))
 From (
       Select A.RN
             ,B.*
        From ( Select RN=Row_Number() over (Order by (select null)),* from string_split(@DataString,':') ) A
        Cross Apply string_split(A.Value,',') B
      ) A
 Group By RN

Upvotes: 1

Related Questions