Parsing A Text String - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Parsing A Text String

Author

Topic

gad
Starting Member

14 Posts

Posted - 2009-09-16 : 22:06:37

Hello,
I have a field such as this:
'State=Pennsylvania.Region=Bucks.RegionType=County.SearchType=Active'
and I'd like to parse it out the parts (delimited by '.') into columns. I got the parsename function to work by using:

Declare @ObjectName VarChar(1000)
Set @ObjectName = 'State=Pennsylvania.Region=Bucks.RegionType=County.SearchType=CommercialRealEstate'
Select @ObjectName

SELECT
PARSENAME(@ObjectName, 4) as col1,
PARSENAME(@ObjectName, 3) as col2,
PARSENAME(@ObjectName, 2) as col3,
PARSENAME(@ObjectName, 1) as col4

But, I'm limited by only 4 parts (my data can have more than 4 parts). Is there another productive solution to step into? Thank You!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-16 : 22:30:24

use fnParseString

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-17 : 02:17:05

[code]
declare @s varchar(8000)
set @s='State=Pennsylvania.Region=Bucks.RegionType=County.SearchType=Active'

SELECT
SUBSTRING(@s, number , CHARINDEX('.', @s + '.', number ) - number )
FROM
(
SELECT NUMBER FROM MASTER..SPT_VALUES WHERE TYPE='P'
) numbers
WHERE
SUBSTRING( '.' + @s, number , 1 ) = '.'[/code]

Madhivanan

Failing to plan is Planning to fail

gad
Starting Member

14 Posts

Posted - 2009-09-18 : 16:32:15

Thank you very much for the suggestions. Both came in very handy.

GAD

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-19 : 02:13:15