Return multiple pattern matches from single field

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

SQL Server Development (2000)

Return multiple pattern matches from single field

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-09-29 : 07:54:32

Keith writes "Is it possible to return multiple pattern matches from a single text field?

I'm querying a database of help tickets, based on a particular customer number, where the help ticket resolution field (text data type) can contain one or more bug id's.

The pattern to match these bug id's is:

BUG[0-9][0-9][0-9][0-9][0-9][0-9]

The SQL I have so far:

select bugnum=substring(resolution, patindex('%BUG[0-9][0-9][0-9][0-9][0-9][0-9]%', resolution),8) 
from tickets
where resolution like '%BUG[0-9][0-9][0-9][0-9][0-9][0-9]%'
and ticket_num in (
                  select ticket_num 
                  from tickets
		  where cust_num = 999999
                  )

This SQL returns the first bug id, but does not return other bug id's that may exist in the resolution field. Is their a way to return all matches from one text field in the same record?

I'm using SQL Server 2000 SP4 under Windows 2000 Server SP4.

Thanks for all your help!!
-Keith"

SamC
White Water Yakist

3467 Posts

Posted - 2005-09-29 : 09:20:21

Your problem might be solved with a variation on the algorithm used in this article

Is this query

select bugnum=substring(resolution, patindex('%BUG[0-9][0-9][0-9][0-9][0-9][0-9]%', resolution),8) 
  from tickets
  where resolution like '%BUG[0-9][0-9][0-9][0-9][0-9][0-9]%'
  and ticket_num in (
                  select ticket_num 
                  from tickets
		  where cust_num = 999999
                  )

doing the same thing as:

select bugnum=substring(resolution, patindex('%BUG[0-9][0-9][0-9][0-9][0-9][0-9]%', resolution),8) 
from tickets
where resolution like '%BUG[0-9][0-9][0-9][0-9][0-9][0-9]%'
and cust_num = 999999

Kristen
Test

22859 Posts

Posted - 2005-09-29 : 09:42:27

"This SQL returns the first bug id, but does not return other bug id's that may exist in the resolution field. Is their a way to return all matches from one text field in the same record?"

"Split" the resolution field, and then UNION (not UNION ALL) then onto the results?

That's probably the same as the Article Sam suggested, but I just mention it in case saying ti differently provides you with some creative input!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources