We help IT Professionals succeed at work.

How to use substring to split a string value

jjliu4492
jjliu4492 asked
on
767 Views
Last Modified: 2012-05-11
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
Comment
Watch Question

Commented:
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]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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?
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
>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...

Author

Commented:
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?
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
please see my comment above.

Author

Commented:
What is your code for dbo.ParmsToList?
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
it's in the article I linked above
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.