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

Transact-SQL (2000)

Parsing

Author

Topic

ladiaocb
Yak Posting Veteran

57 Posts

Posted - 2006-01-19 : 16:54:09

Hey All,

I have a table with a column called Comments. Looks like

Comments---
[Customer Name] Matt Hughes [SS#] 123456789 [Phone#] 123 456 6789 [Call Duration] 02:08 [HV/HP] xx/xx

How do parse out the data seperately? (I.e. I want to extract the Customer Name and the SS# into seperate tables). This format is standardized so every row has the same tags ([Customer Name], [SS#], etc.)

As always thanks for your help in advance.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-19 : 17:08:27

Use Charindex to search for the beginning of each tag, and use it again to find the end of the tag (beginning of the next tag).

This type of coding is never fun, but check out the varchar functions available in SQL server and with a little creativity you can get the job done.

ladiaocb
Yak Posting Veteran

57 Posts

Posted - 2006-01-19 : 17:18:19

I'll give it a shot. If anyone else has an example script for the example I have above that would be greatly appreciated.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-20 : 02:09:47

Start with this



Declare @data varchar(100)
set @data='[Customer Name] Matt Hughes [SS#] 123456789 [Phone#] 123 456 6789 [Call Duration] 02:08 [HV/HP] xx/xx'
Select substring(@data,1,charindex('[SS#]',@data)-1) as Name_Part,
substring(@data,charindex('[SS#]',@data),len(@data)) as Other_Part

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources