Problem detecting string pattern

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

Transact-SQL (2008)

Problem detecting string pattern

Author

Topic

WindChaser
Posting Yak Master

225 Posts

Posted - 2012-06-20 : 08:30:35

Hi folks,

I have the following 2 tables:

AppointmentID Date Time Account
100 2009/12/01
1001 2012/06/19 10:20 25
1002 2012/06/19 10:40 126
1003 2012/06/19 11:00 527
1004 2012/06/19 12:00 5
1005 2012/06/19 13:00 252

NoteID AppsWithNote
1 *1001*
2 *1002*,*1003*
3 *100*

where the Appointments column is varchar because one note may apply to several appointments. The * character is present in order to ensure proper pattern detection (e.g. that pattern "100" will not be detected as part of 1003).

My objective is to detect all appointments which do not have notes. Here's my code:

DECLARE @AllAppsWithNotes varchar(Max)

SELECT @AllAppsWithNotes = ''

SELECT @AllAppsWithNotes = COALESCE(@AllAppsWithNotes + ',', '')
+ AppsWithNote From Table2

SELECT @AllAppsWithNotes = RIGHT(AllAppsWithNotes,
LEN(AllAppsWithNotes) - 1)

SELECT @AllIDs 'Query #1
SELECT * from Table1 Where
(SELECT PATINDEX('*' + CAST(AppointmentID AS VARCHAR) + '*', @AllAppsWithNotes)) = 0 'Query #2

The results are as follows:
For Query #1: @AllAppsWithNotes = *1001*,*1002*,*1003*,*100*
For Query #2: No records.

My objective (and what I expected) from Query #2 is to get:

AppointmentID Date Time Account
1004 2012/06/19 12:00 5
1005 2012/06/19 13:00 252

What the heck am I doing wrong here? Thanks!

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-06-20 : 10:55:55

do you want a quick fix that will still continue to cause you all kind of issue in the long run or are you interested in an overhaul of what you have that will scale and cause you joy and happiness?

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-06-20 : 11:05:42

quick fix/hack that will forever haunt you


declare @app table(AppointmentID int, Date varchar(10), Time varchar(10), Account int)

insert into @app
SELECT 100, '2009/12/01', null, null
UNION 
SELECT 1001, '2012/06/19', '10:20', '25'
UNION 
SELECT 1002, '2012/06/19', '10:40', '126'
UNION 
SELECT 1003, '2012/06/19', '11:00', '527'
UNION 
SELECT 1004, '2012/06/19', '12:00', '5'
UNION 
SELECT 1005, '2012/06/19', '13:00', '252'

declare @AppsWithNote table(NoteID int, AppsWithNote varchar(max))

insert into @AppsWithNote
SELECT 1, '*1001*'
UNION
SELECT 2, '*1002*,*1003*'
UNION
SELECT 3, '*100*'

;with tmp(NoteID, DataItem, Data) as (
select NoteID,  LEFT(AppsWithNote, CHARINDEX(',',AppsWithNote+',')-1),
    STUFF(AppsWithNote, 1, CHARINDEX(',',AppsWithNote+','), '')
from @AppsWithNote
union all
select NoteID, LEFT(Data, CHARINDEX(',',Data+',')-1),
    STUFF(Data, 1, CHARINDEX(',',Data+','), '')
from tmp
where Data > ''
) 
select a.*
  from @app a
  left join tmp t
  on a.AppointmentID = CAST( REPLACE(DataItem,'*','') AS INT)
  where t.DataItem IS NULL
order by NoteID

what you really need is the following table


create table dbo.AppsWithNote(NoteID, AppointmentID)
ALTER TABLE [dbo].[AppsWithNote] ADD  CONSTRAINT [PK_AppsWithNote] PRIMARY KEY CLUSTERED 
(
	NoteID ASC,
	AppointmentID ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

forget this whole pattern detecting thingy

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-20 : 11:29:05

quote:
Originally posted by yosiasz
what you really need is the following table
...
forget this whole pattern detecting crap

My thoughts exactly, although I would strike the last word

Another approach, which I think should work (at least gives the same data when using yosiasz's test data).

SELECT * FROM @app a
WHERE NOT EXISTS
(
	SELECT * 
	FROM @AppsWithNote n 
	WHERE n.AppsWithNote LIKE '%*'+CAST(a.AppointmentID AS VARCHAR(32))+'%*'
)

WindChaser
Posting Yak Master

225 Posts

Posted - 2012-06-20 : 15:37:48

Thanks people. Ya, actually, creating a separate table with a few int columns would take next to nothing in terms of space and would just make life easier. Thanks for your guidance!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources