Best way to Split strings? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Best way to Split strings?

Author

Topic

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2014-06-09 : 06:02:50

I need to loop over a string and split it up, but what would be the best way before I dive in and try and work it out?
I can do this in VBA but not sure in T-SQL, I am using sql 2005.

I have a table with two columns:
Item Code (Column1) = Jumper7859
Fabric Information (Column2) = FABRIC: Cotton, 75%, Linen, 25%

Column 2 I need to split and insert into a table as split like so:

1, Jumper7589, FABRIC, Cotton, 75
2, Jumper7589, FABRIC, Linen, 25

I've looked up maybe using PATINDEX like I have start below and then add some sort of loop and insert into table.

DECLARE @MyValue varchar(400)
DECLARE @MyValue2 varchar(400)
DECLARE @MyPosition varchar(400)

SET @MyValue = 'FABRIC: Cotton, 75%, Linen, 25%';
SET @MyPosition = PATINDEX('%:%',@MyValue);

--SELECT PATINDEX('%:%',@MyValue)
SELECT LEFT(@MyValue,@MyPosition-1)
SET @MyValue2 = RIGHT(@MyValue,(LEN(@MyValue) - @MyPosition) )
SELECT @MyValue2
GO

But am I heading in the right direction or should I be looping on each individual character in the string then split it using LEFT() RIGHT().

All help welcome as I don't want to proceed with the above theory and find out I end up in a world of hurt a few hours down the line.

Thanks.

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2014-06-09 : 06:11:52

Hmm may have found my first problem on this I can't use PATINDEX to find a percentage sign PATINDEX('%%%%', 'FABRIC: Cotton, 75%, Linen, 25%')

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2014-06-09 : 06:17:20

OK looks like I can do this to search for %: PATINDEX('%[%]%', 'FABRIC: Cotton, 75%, Linen, 25%')

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2014-06-09 : 06:22:24

I think I have kind of got it splitting doing this then need to loop to end of string in case more types... like 'FABRIC: Cotton, 25%, Linen, 25%', Wool, 25%', Rubber, 25%';

DECLARE @MyValue varchar(400)
DECLARE @MyValue2 varchar(400)
DECLARE @MyPosition varchar(400)

-- start set value
SET @MyValue = 'FABRIC: Cotton, 75%, Linen, 25%';

SELECT @MyValue

-- MAIN FABRIC TYPE
SET @MyPosition = PATINDEX('%:%',@MyValue);
SELECT LEFT(@MyValue,@MyPosition-1)

-- FABRIC TYPE 1
SET @MyValue = RIGHT(@MyValue,(LEN(@MyValue) - @MyPosition-1) )
-- SELECT @MyValue
SET @MyPosition = PATINDEX('%,%',@MyValue);
SELECT LEFT(@MyValue,@MyPosition-1)

-- FABRIC TYPE 1 PERCENTAGE
SET @MyValue = RIGHT(@MyValue,(LEN(@MyValue) - @MyPosition-1) )
-- SELECT @MyValue
SET @MyPosition = PATINDEX('%[%]%',@MyValue);
SELECT LEFT(@MyValue,@MyPosition-1)

-- FABRIC TYPE 2
SET @MyValue = RIGHT(@MyValue,(LEN(@MyValue) - @MyPosition-1) )
-- SELECT @MyValue
SET @MyPosition = PATINDEX('%,%',@MyValue);
SELECT LEFT(@MyValue,@MyPosition-1)

-- FABRIC TYPE 2 PERCENTAGE
SET @MyValue = RIGHT(@MyValue,(LEN(@MyValue) - @MyPosition-1) )
-- SELECT @MyValue
SET @MyPosition = PATINDEX('%[%]%',@MyValue);
SELECT LEFT(@MyValue,@MyPosition-1)

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2014-06-09 : 06:23:42

Correction:

needs to be @MyPosition-2

-- FABRIC TYPE 2
SET @MyValue = RIGHT(@MyValue,(LEN(@MyValue) - @MyPosition-2) )
-- SELECT @MyValue
SET @MyPosition = PATINDEX('%,%',@MyValue);
SELECT LEFT(@MyValue,@MyPosition-1)

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2014-06-09 : 06:43:38

Searching the INternet and maybe I head down this route but I would have to amend to search for : then,

DECLARE @valueList varchar(8000)
DECLARE @pos INT
DECLARE @len INT
DECLARE @value varchar(8000)

SET @valueList = 'FABRIC: Cotton, 75%, Linen, 25%,'

SET @pos = 0
SET @len = 0

WHILE CHARINDEX(',', @valueList, @pos+1)>0
BEGIN
set @len = CHARINDEX(',', @valueList, @pos+1) - @pos
set @value = SUBSTRING(@valueList, @pos, @len)
-- SELECT @pos, @len, @value /*this is here for debugging*/

SELECT @value
SET @pos = CHARINDEX(',', @valueList, @pos+@len) +1
END

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2014-06-09 : 08:55:56

A link to one of Visakhs awesome posts:
http://visakhm.blogspot.in/2010/02/parsing-delimited-string.html

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2014-06-09 : 09:59:05

Thanks Don, I was just trying the code below I have just done, but the link looks good, going to dive into the link now.

DECLARE @position INT
DECLARE @LastPosition INT

DECLARE @MaterialPart VARCHAR(500)
DECLARE @MaterialCategory VARCHAR(500)
DECLARE @MaterialPercentage VARCHAR(500)

DECLARE @String VARCHAR(500)
SET @String = 'FABRIC: Cotton, 75%, Linen, 25%, LINING: Wool, 40%, Rubber, 60%'

SET @position = 1
SET @LastPosition = 0

WHILE @position <= DATALENGTH(@string)

BEGIN

-- Get Material Part
IF SUBSTRING(@string, @position, 1) = ':'
BEGIN
SET @MaterialPart = RTRIM(LTRIM(SUBSTRING(@string,@LastPosition,@position - @LastPosition)))
SET @LastPosition = @position + 1
END

-- Material Category
IF SUBSTRING(@string, @position, 1) = ','
BEGIN
SET @MaterialCategory = RTRIM(LTRIM(SUBSTRING(@string,@LastPosition,@position - @LastPosition)))
SET @LastPosition = @position + 1
END

-- Material %
IF SUBSTRING(@string, @position, 1) = '%'
BEGIN
SET @MaterialPercentage = RTRIM(LTRIM(SUBSTRING(@string,@LastPosition,@position - @LastPosition)))
SET @LastPosition = @position + 1

SELECT @MaterialPart,@MaterialCategory,@MaterialPercentage

END

SET @position = @position + 1

END

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2014-06-09 : 10:02:46

Thanks Don for the link but looking at it a bit more don't think it will work in my case as it only has one Delimiter and I need to split on a few different ones like in my Posted - 06/09/2014 : 09:59:05.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2014-06-09 : 10:35:33

I see what you mean now. That is some ugly data. Too bad it doesn't come in normalized.
I found this post http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=169909 , which seems to be close to the same thing you are doing.

Add the information there (the last post to be specific) to this http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033&whichpage=2

And it may just come together.

however, if the code you have already works, you would not need to change it.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2014-06-10 : 09:13:35

Thanks Don, Yeah the lines are a real bitch to work with don't know whoever has inputted them like this and now I need to get them out in table format. My code seems to do the trick so I'm going to add a CURSOR and FETCH to work on each line then INSERT INTO a new Table about 2500 lines in total. I haven't used CURSORS before and on the web everyone seems to be slating them as no good stay away from CURSORS, what's your take on them, reckon OK for this operation... not sure how else to get each row into the code above to work on.

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2014-06-10 : 09:15:42

Yeah your link above: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033&whichpage=2

Looks good... just checking it out now.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2014-06-10 : 10:25:27

cursors have their use. MOST things can be done in a set based manner, which SQL Server is VERY good at. SOME things just take a cursor. In this case, i do not know if you could do it set based. Peter probably could, but he posts sporadically. If this is a one-off, it really does not matter. If it is going to be a scheduled item, then it should be engineered as best as possible.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2014-06-13 : 05:04:20

Thanks Dan, that will be my weekend project between World Cup football matches to change my code from CURSOR to set based, always nice to have a challenge, thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources