How to Split String

Question

Post reply

How to Split String

Stylez

SSCrazy

Points: 2195
More actions
August 29, 2013 at 11:09 am

#276628

Good Morning Guys,
Is There a simple way of splitting A Space Delimited String and putting it into 2 separate fields
(e.G. Engine Make And Model ('PeterBilt MT2011')) the parameter would be coming from a Single field(Engine) from a table named Units which i need to split to again insert to another table
so from
EngineMakeAndModel
PeterBilt MT20100
To
EngineMake Model
Peterbild MT2100
and is it possible for this not to be put into a UDF?
Best Regards,
Noel

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Take a look at Jeff Moden Splitter and come back if you have questions or need help.

http://www.sqlservercentral.com/articles/Tally+Table/72993/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

If your actual data is as simple as the one example you could also use PARSENAME.

select PARSENAME(replace(EngineMakeAndModel, ' ', '.'), 2), PARSENAME(replace(EngineMakeAndModel, ' ', '.'), 1)

from (values('PeterBilt MT20100')) as x(EngineMakeAndModel)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

The correct way to split a string using T-SQL would be to use Jeff's splitter as Louis mentioned. That said, what you are doing is quite simple; you could do this:

SELECTLEFT(EngineMakeAndModel,CHARINDEX(' ',EngineMakeAndModel)),

RIGHT(EngineMakeAndModel,LEN(EngineMakeAndModel)-CHARINDEX(' ',EngineMakeAndModel))

FROM

(values('PeterBilt MT20100')) as x(EngineMakeAndModel)

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Stylez SSCrazy Points: 2195 More actions · Answer 4

Good Afternoon Guys,

The EngineMakeModel is The Raw Column that is not Yet Concatenated and would need to be split into th

EngineMake Columnd and Model Column Separately. The Input would also come from a Sql Statement( E.G. Select EngineMakeModel from Units)

Best Regards,

Noel

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Alan.B (8/29/2013)
The correct way to split a string using T-SQL would be to use Jeff's splitter as Louis mentioned. That said, what you are doing is quite simple; you could do this:
SELECTLEFT(EngineMakeAndModel,CHARINDEX(' ',EngineMakeAndModel)),
RIGHT(EngineMakeAndModel,LEN(EngineMakeAndModel)-CHARINDEX(' ',EngineMakeAndModel))
FROM
(values('PeterBilt MT20100')) as x(EngineMakeAndModel)

What do you mean by the correct way? Using PARSENAME is very common for really simple splits like this.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Stylez SSCrazy Points: 2195 More actions · Answer 6

SELECTLEFT(EngineMakeAndModel,CHARINDEX(' ',EngineMakeAndModel)),

RIGHT(EngineMakeAndModel,LEN(EngineMakeAndModel)-CHARINDEX(' ',EngineMakeAndModel))

FROM

(values('engines')) as x(select engine from units)

can i do the one above?

I want the Input to come from the Select Statement i have provided

Much Thanks

Noel

Alan Burstein SSC Guru Points: 61141 More actions · Answer 7

Sean Lange (8/29/2013)
Alan.B (8/29/2013)
The correct way to split a string using T-SQL would be to use Jeff's splitter as Louis mentioned. That said, what you are doing is quite simple; you could do this:
SELECTLEFT(EngineMakeAndModel,CHARINDEX(' ',EngineMakeAndModel)),
RIGHT(EngineMakeAndModel,LEN(EngineMakeAndModel)-CHARINDEX(' ',EngineMakeAndModel))
FROM
(values('PeterBilt MT20100')) as x(EngineMakeAndModel)
What do you mean by the correct way? Using PARSENAME is very common for really simple splits like this.

I'm sorry Sean, my comment came out wrong. "the correct way to split a string" was an endorsement of Jeff's Splitter and my way of saying "I second what Louis said"; it had nothing to do with ~~this thread~~ Stylez' question. I was also not trying to take issue with the method you posted but rather trying to contribute other solutions.

Edit: added one last sentence; corrected typo.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

Alan.B (8/29/2013)
Sean Lange (8/29/2013)
Alan.B (8/29/2013)
The correct way to split a string using T-SQL would be to use Jeff's splitter as Louis mentioned. That said, what you are doing is quite simple; you could do this:
SELECTLEFT(EngineMakeAndModel,CHARINDEX(' ',EngineMakeAndModel)),
RIGHT(EngineMakeAndModel,LEN(EngineMakeAndModel)-CHARINDEX(' ',EngineMakeAndModel))
FROM
(values('PeterBilt MT20100')) as x(EngineMakeAndModel)
What do you mean by the correct way? Using PARSENAME is very common for really simple splits like this.
I'm sorry Sean, my comment came out wrong. "the correct way to split a string" was an endorsement of Jeff's Splitter and my way of saying "I second what Louis said"; it had nothing to do with ~~this thread~~ Stylez' question. I was also not trying to take issue with the method you posted but rather trying to contribute other solutions.
Edit: added one last sentence; corrected typo.

I am certainly a very strong advocate of his splitter (it is in my signature). It seems a bit overkill to me for something so simple. As always it is great to see multiple examples of how to do something.

The problem with using the splitter on something like this is that the splitter returns each value on a new row instead in another column. If you use the splitter you will have to do a cross tab to get the values into multiple columns again.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

Stylez (8/29/2013)
SELECTLEFT(EngineMakeAndModel,CHARINDEX(' ',EngineMakeAndModel)),
RIGHT(EngineMakeAndModel,LEN(EngineMakeAndModel)-CHARINDEX(' ',EngineMakeAndModel))
FROM
(values('engines')) as x(select engine from units)
can i do the one above?
I want the Input to come from the Select Statement i have provided
Much Thanks
Noel

The reason I used the syntax with values that probably looks foreign to you is because I had no table to work with. I created an "on-the-fly" result set. You have the advantage of having a table to select from.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Stylez SSCrazy Points: 2195 More actions · Answer 10

Hi Guys,

I would need to run the function across a querry that returns more than one values

do i need to create a cursor for this to be able to use the splitter function?

Best Regards,

Noel

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

Stylez (8/29/2013)
Good Afternoon Guys,
The EngineMakeModel is The Raw Column that is not Yet Concatenated and would need to be split into th
EngineMake Columnd and Model Column Separately. The Input would also come from a Sql Statement( E.G. Select EngineMakeModel from Units)
Best Regards,
Noel

OK I have to admit you have me confused now. You originally asked how can you split two values but now it sounds like you already have them split? Or maybe you meant to say "not yet SPLIT" instead of "Concatenated"?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 12

Stylez (8/29/2013)
Hi Guys,
I would need to run the function across a querry that returns more than one values
do i need to create a cursor for this to be able to use the splitter function?
Best Regards,
Noel

Absolutely not!!! You don't need a cursor here at all. Maybe if you can post actual ddl and a few rows of data that is representative of your actual data we can show you how to do this.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Stylez SSCrazy Points: 2195 More actions · Answer 13

Select engine From Units Where UnitNumber = '170272'

Result:

engine

------------

CAT C-15

What i Want to Achieve:

select MFGCODE,model from UASSEM where UNITID = '170272'

MFGCODE model

------------ ------------

CAT C-15

Sean Lange SSC Guru Points: 286590 More actions · Answer 14

Stylez (8/29/2013)
Select engine From Units Where UnitNumber = '170272'
Result:
engine
------------
CAT C-15
What i Want to Achieve:
select MFGCODE,model from UASSEM where UNITID = '170272'
MFGCODE model
------------ ------------
CAT C-15

I don't get it. You have the data concatenated in one table and split in another? Remember we can't see your screen, we have no idea what your tables look like and we don't know what you are really trying to do. This is why we ask for ddl and sample data.

Does something like this help?

select PARSENAME(replace(engine, ' ', '.'), 2), PARSENAME(replace(engine, ' ', '.'), 1)

From Units Where UnitNumber = '170272'

SELECTLEFT(engine,CHARINDEX(' ',engine)),

RIGHT(engine,LEN(engine)-CHARINDEX(' ',engine))

From Units Where UnitNumber = '170272'

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/