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 - 2005-07-15 : 13:59:49

I need help with a query. I am trying to parse some data out of a column that is used to keep comments. I have the following:

CommentsTbl

CommentsID Comments
1 Spoke with customer SSN: 123-45-6789

I want to extract the SSN number from this column. The SSN is not in a fixed location so i can't use substring. Any help is always appreciated!!

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-07-15 : 14:35:27

What is the datatype of the <comments> column ?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-07-15 : 14:44:13

if the answer to ehorn's question is not text then...

I guess you need to establish some "rules" ie:
social sec number will always be the 11 characters that follow: "SSN: "
or
social sec number will be the only value in the column that is in format nnn-nn-nnnn.

Here might be one way (following the second rule above)


set nocount on
declare @tb table (commentsid int, comments varchar(2000))
insert @tb
select 1, '1 Spoke with customer SSN: 123-45-6789' union
select 2, '1 Spoke with customer phone: 123-456-1234; SSN: 123-45-6789' union
select 2, '1 Spoke with customer name: Tim'

select	commentsid
	,substring(comments, idx, 11) ssn
	,comments
from	(
	select	commentsid
		,comments
		,patindex('%[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]%', comments) idx
	from	@tb
	)a
where	idx > 0