Convert space seperated string into rows - but with associated id field

Question

Convert space seperated string into rows - but with associated id field

olibbhq

SSCarpal Tunnel

Points: 4034
More actions
January 14, 2013 at 11:05 am

#393935

Hi, I am stuck with getting the data into rows.
I would like to turn
ID Well_id Figure
1 20 1 2a 5 6
2 22 3 4 22a
into
ID Well_id Figure
1 20 1
1 20 2a
1 20 5
1 20 6
2 22 3
2 22 4
2 22 2
Currently I have
with tmp(ID, Well_id, DataItem, Figure) as (
select ID, Well_id, cast(LEFT(Figure, CHARINDEX(',',Figure +',')-1) as varchar(50)),
STUFF(Figure, 1, CHARINDEX(',',Figure+','), '')
from Citation_all_Region
where figure not like '[a-z]%'
union all
select ID, Well_id, cast(LEFT(Figure, CHARINDEX(' ',Figure+',')-1) as varchar(50)),
STUFF(Figure, 1, CHARINDEX(',',Figure +','), '')
from tmp
where Figure > '' --and figure not like '[a-z]%'
)
select ID, Well_id, DataItem
from tmp
order by ID
It doesnt like text to start (I dont know why so I had to put a where clause in) and it is still coming up with
Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.
Any help would be greatfully appreciated. If you know a good web example you can show me that would be fantastic.
Cheers,
Oliver

Viewing 4 posts - 1 through 3 (of 3 total)

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

Greg Snidow SSCoach Points: 16108 More actions · Answer 1

Oliver, you could try this... http://www.sqlservercentral.com/articles/Tally+Table/72993

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

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

Yeah read that article and then the code is pretty simple.

;with cte (ID, Well_id, Figure) as

(

select 1, 20, '1 2a 5 6' union all

select 2, 22, '3 4 22a'

)

select ID, Well_id, s.Item

from cte

cross apply dbo.DelimitedSplit8K(cte.Figure, ' ') s

Notice how I included sample data in a readily consumable format? That is something you should do in future posts. It makes it a lot easier for us to help.

_______________________________________________________________

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/

olibbhq SSCarpal Tunnel Points: 4034 More actions · Answer 3

Thank you, this worked perfectly. In future I will provide test data in this way.

Thanks again,

Oliver