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

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

quote:

---

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

---

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