Post reply

Efficient way to split a string

  • August 1, 2019 at 4:31 pm

    #3667968

    Hi All,

    I have a table that is approx 65k rows long. WIthin the table is a varchar(max) column which contains a string as per below

     

    [ { "Code": 123, "Label": "Animal", "Value": "Cow", "RecordYear": "2017-03-09T11:19:58.94" }, { "Code": 456, "Label": "Type", "Value": "Mammal", "RecordYear": "2017-03-09T11:20:26.697" }, { "Code": 789, "Label": "GoodFor", "Value": "Milk","RecordYear": "2017-03-09T11:20:44.65" }, { "Code": 101, "Label": "Annoys", "Value": "Vegans", "RecordYear": "2017-03-09T11:23:40.54" }]

    however can be a lot longer,

    I have a solution that uses the 8k split string function below but it is extremely slow.

    Can anyone help me out.

    SELECT   ID
    ,Case WHen Item like ',%' then 

    Substring((LTRIM(RTRIM(REplace(Replace(ITEM, '{', ''), '}', '')))) ,4,len(LTRIM(RTRIM(REplace(Replace(ITEM, '{', ''), '}', '')))))

    Else 
    (LTRIM(RTRIM(REplace(Replace(ITEM, '{', ''), '}', ''))))
    END AS ValueItem
    ,ItemNumber

    INTO #B

    FROM #A
    CROSS APPLY dbo.[DelimitedSplit8K](Replace(SUBSTRING(Form, 3, len(form) - 3), '"', ''), '}') B

    --====================================================================================================================================================

    Select 

    ID ,  
    Replace(SUBSTRING(ValueItem, CHARINDEX('Code: ',ValueItem),CHARINDEX(', Label: ',ValueItem)-1),'Code: ','') as Code,
    Replace(SUBSTRING(ValueItem, CHARINDEX('Label: ',ValueItem),CHARINDEX('Value: ',ValueItem)-(CHARINDEX('Label: ',ValueItem)+2)),'Label: ','') as Label,
    Replace(Case 
    When  ValueItem like '%RecordYear%' 
    then  SUBSTRING(ValueItem, CHARINDEX('Value: ',ValueItem), CHARINDEX('RecordYear: ',ValueItem)-(CHARINDEX('Value: ',ValueItem)+2)) 
    Else SUBSTRING(ValueItem, CHARINDEX('Value: ',ValueItem),Len(ValueItem))
    END ,'Value: ','') as Value,
    Case 
    When  ValueItem like '%RecordYear%' 
    then  REPLACE(SUBSTRING(ValueItem, CHARINDEX('RecordYear: ',ValueItem), Len(ValueItem)) ,'RecordYear: ','')
    Else NULL
    END  as RecordYear


    FROM #B

  • August 1, 2019 at 6:48 pm

    #3668016

    Duplicate post. See replies here:

    https://www.sqlservercentral.com/forums/topic/efficient-way-to-split-a-string-2

    --Vadim R.

  • August 2, 2019 at 8:48 pm

    #3668395

    Closing

Viewing 3 posts - 1 through 2 (of 2 total)

The topic ‘Efficient way to split a string’ is closed to new replies.