Help splitting a string with several delimeters

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 2000 Forums

Transact-SQL (2000)

Help splitting a string with several delimeters

Author

Topic

Planet_x
Starting Member

15 Posts

Posted - 2009-08-27 : 11:39:26

I am trying to split the following string which is held in one field of the table PTM_PARCEL_TEST. The problem I am having is I need to make a split function that uses several different delimeters.

The string from the table is in the field as -
NW¼ Sec 31 38-27-W4

What I need to accomplish is break the string into 5 different fields: Quarter, Section, Township, Range and Meridian. The delimeters would be the 1/4 symbol, Sec, - and 'W'. My end goal is to split the string and have it show up in a second table (STRING_TEST) as the following -

[Quarter] NW
[Section] 31
[Township] 38
[Range] 27
[Meridian] 4

There is always the same delimeters (1/4, Sec, -, W) and each string is formatted the same. Just not sure where to start if someone could suggest a similar function or point me in the right direction any help greatly appreciated, thanks!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-28 : 03:05:51

declare @string varchar(100)
declare @data table(data varchar(1000))

set @string ='NW¼ Sec 31 38-27-W4'
insert into @data
select SUBSTRING(@string,1,CHARINDEX('¼',@string)-1)
set @string =SUBSTRING(@string,CHARINDEX('¼',@string)+1,LEN(@string))
set @string =SUBSTRING(@string,CHARINDEX('Sec',@string)+4,LEN(@string))
set @string =replace(replace(@string,' ','.'),'-','.')
insert into @data
select PARSENAME(@string,4)
union all
select PARSENAME(@string,3)
union all
select PARSENAME(@string,2)
union all
select PARSENAME(@string,1)

select data from @data

Madhivanan

Failing to plan is Planning to fail

Planet_x
Starting Member

15 Posts

Posted - 2009-08-28 : 09:45:45

thank you very much, makes sense i was trying to do this using charindex myself but i did not understand the syntax correctly, now I do! I really appreciate your help!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-28 : 10:33:37

quote:
Originally posted by Planet_x

thank you very much, makes sense i was trying to do this using charindex myself but i did not understand the syntax correctly, now I do! I really appreciate your help!

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources