Extracting Data From String Column

Question

Post reply

Extracting Data From String Column

fgrubercpa

Say Hey Kid

Points: 661
More actions
November 19, 2013 at 7:36 pm

#395859

Good evening Gentlemen,
Rows of Text Looks like this one:
|86.1234 32.123|45.1254 23.1288|46.1785 17.1266|48.1324 65.4568|etc... (variable length record)
___________________________
The number of digits after the decimal varies. There is a single space between the two numbers (which are both inside the ||).
I need to extract each pair of numbers (a,b) and place them in a new table, each within their respective column, i.e. the first number of the pair goes under column a, the second column b. Each record has a variable length set of pairs. The rows (pairs) need to be ordered in the same order as they appear in the text (left to right).
Any assistance that you could offer would be greatly appreciated.
thanks
Spatio

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

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

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 1

Spatio - I would use Jeff Moden's function for splitting strings:

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

You would then have each "row" broken down and then you can write a statement to determine if the value is in the "A" column or the "B" column, by using substring.

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

fgrubercpa (11/19/2013)
Good evening Gentlemen,
Rows of Text Looks like this one:
|86.1234 32.123|45.1254 23.1288|46.1785 17.1266|48.1324 65.4568|etc... (variable length record)
___________________________
The number of digits after the decimal varies. There is a single space between the two numbers (which are both inside the ||).
I need to extract each pair of numbers (a,b) and place them in a new table, each within their respective column, i.e. the first number of the pair goes under column a, the second column b. Each record has a variable length set of pairs. The rows (pairs) need to be ordered in the same order as they appear in the text (left to right).
Any assistance that you could offer would be greatly appreciated.
thanks
Spatio

The code below will do your splitting by making use of Jeff Moden's DelimitedSplit8K function posted at http://www.sqlservercentral.com/articles/Tally+Table/72993/. If you aren't familiar with it yet, take the time to get acquainted. It's well worth the read.

with cte as (

select '|86.1234 32.123|45.1254 23.1288|46.1785 17.1266|48.1324 65.4568|' value),

cte2 as (

select itemnumber, item

from cte

cross apply util.dbo.DelimitedSplit8K (cte.value, '|')

where len(item) > 0)

select cte2.item original_item,

column_a = max(case when s.itemnumber = 1 then s.Item end),

column_b = max(case when s.itemnumber = 2 then s.Item end)

from cte2

cross apply util.dbo.DelimitedSplit8K(cte2.Item, ' ') s

group by cte2.Item;

The data is split by pipe, then by space. Since you only have 2 numbers in each pair, you only have to allow for 2 return columns.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

fgrubercpa Say Hey Kid Points: 661 More actions · Answer 3

Thanks Ed,

I will dig into this bright and early tomorrow am, and I mean BRIGHT and EARLY,

thanks agin,

Spatio