Avatar of jjliu4492
jjliu4492
 asked on

How to use substring to split a string value

I have a string value, that I want to separate into 6 strings. The delimiter is _. So for this string, the six values would be: How would I go about splitting the string in SQL Server 2008?

TAMPA, FL_VASTEC__ACCESSIBILITY TRAINING_508_FIRST

TAMPA, FL
VASTEC
null
ACCESSIBILITY TRAINING
508
FIRST
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
Guy Hengel [angelIII / a3]

8/22/2022 - Mon
kaminda

You can use the below function. Call it by passing the string value and the delimiter. It will return a table with splitted values as rows.
CREATE FUNCTION dbo.Split
(
	@RowData nvarchar(2000),
	@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
	Id int identity(1,1),
	Data nvarchar(100)
) 
AS  
BEGIN 
	Declare @Cnt int
	Set @Cnt = 1

	While (Charindex(@SplitOn,@RowData)>0)
	Begin
		Insert Into @RtnValue (data)
		Select 
			Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

		Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
		Set @Cnt = @Cnt + 1
	End
	
	Insert Into @RtnValue (data)
	Select Data = ltrim(rtrim(@RowData))

	Return
END

Open in new window

Guy Hengel [angelIII / a3]

it depends a bit what you want to do with the values ...
I also wrote an article about this kind of thing: https://www.experts-exchange.com/A_1536.html
ASKER CERTIFIED SOLUTION
RGBDart

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
jjliu4492

ASKER
I am trying to split up the field, and save the split values into a new table. Can I do this without using a function, and just use substring?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Guy Hengel [angelIII / a3]

>Can I do this without using a function, and just use substring?
not really. technically yes, but you will multiply the instr() and substring functions...
jjliu4492

ASKER
select * from dbo.fnSplit('TAMPA, FL_VASTEC__ACCESSIBILITY TRAINING_508_FIRST', '_')

Can I have it display as one row, with each field as a column?
SOLUTION
Guy Hengel [angelIII / a3]

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
jjliu4492

ASKER
If I use fnSplit, my result set is:

How can I get a specific row value? For example, I need to insert "VASTEC" into a column in my database.

item
TAMPA, FL
VASTEC
NULL
ACCESSIBILITY TRAINING
508
FIRST

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Guy Hengel [angelIII / a3]

please see my comment above.
jjliu4492

ASKER
What is your code for dbo.ParmsToList?
Guy Hengel [angelIII / a3]

it's in the article I linked above
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23