Efficient way to split a string

• waxb18

  Ten Centuries

  Points: 1058

  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

• rVadim

  Hall of Fame

  Points: 3897

  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.

• Steve Jones - SSC Editor

  SSC Guru

  Points: 715107

  August 2, 2019 at 8:48 pm

  #3668395

  Closing

  Follow me on Twitter: http://www.twitter.com/way0utwest
  Forum Etiquette: How to post data/code on a forum to get the best help
  My Blog: www.voiceofthedba.com