Efficient way to split a string

  • waxb18

    Ten Centuries

    Points: 1058

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

    Hall of Fame

    Points: 3897

    Duplicate post. See replies here:

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

    --Vadim R.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715107

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

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