Using Like in a where clause - 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

General SQL Server Forums

New to SQL Server Programming

Using Like in a where clause

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-02-12 : 08:58:59

How could I add to this stored procedure that I don't want any characters from the Reg field that's has H01 - H037? I know I use Like but don't know how to add it. Thanks.

insert test

Select Sort='1',Reg='NAT', Region='0', Area='00', Dist = '000', Doc = '000', co, cr,
Ans, OverFlow, Aband,Busy,WKDate, DowrDate

From
(select sum(isnull([calls offered],0)) as CO,
sum(isnull([calls received],0)) as CR,
sum(isnull(Answered,0)) as Ans,
sum(isnull(Overflow,0)) as OverFlow,
sum(isnull(Abandoned,0)) as Aband,
sum(isnull(Busy,0)) as Busy,
convert(varchar,wkdate,1) as Wkdate, convert(varchar,dowrdate,1) as dowrdate
from TSRPReport
WHERE Date BETWEEN DATEADD(day, - 4, wkdate) AND wkdate
group by wkdate,dowrdate
)a

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-12 : 09:47:54

Some sample data and wanted output?

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-02-12 : 09:57:41

WHERE Reg NOT LIKE 'H0[1-9]' AND Reg NOT LIKE 'H0[1-2][0-9]' AND Reg NOT LIKE 'H03[0-7]'

although I'm a bit confused that you have "H01" (2 digits) and "H037" (3 digits)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-12 : 10:03:23

quote:
Originally posted by Kristen

WHERE Reg NOT LIKE 'H0[1-9]' AND Reg NOT LIKE 'H0[1-2][0-9]' AND Reg NOT LIKE 'H03[0-7]'

although I'm a bit confused that you have "H01" (2 digits) and "H037" (3 digits)

Until we know the actual pattern I doubt it will work (for data like H010, H009, etc)

Madhivanan

Failing to plan is Planning to fail

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-02-12 : 10:15:49

Sorry about that... here's the data:

H01 - H08

then

H31 - H37

Kristen
Test

22859 Posts

Posted - 2010-02-12 : 10:17:42

H010 = Fine