Help needed with a query 1

[Magnus53]

Hello,

I have a field that is called usrID's. Some examples look like this test1,test2,test3,tester1,tested1 etc.

I need to write a query that will count the number of usrID's based on the alpha part of the id. So say I pass in "test" as a parameter, I want it to count test1,test2,test3 but not tester1, etc.

Can someone tell me the best way to solve this?


Any replies are appreciated.

 

[pkailas]

select usrID from

where left(usrID, 4) = 'test' and left(usrid, 6) <> 'tester'

 

[SQLBill]

Might try:

WHERE userids LIKE 'test[0-9]'

Refer to the BOL for more information on LIKE

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

Posting advice: FAQ481-4875

 

[danceman]

select usrID, count(usrID) from Yourtable WHERE useID LIKE '%test%'

This will count all usrID's that have test in the field

'test%' = all usrID's with test at the first part of the field

Attitude is Everything

 

[SQLBill]

danceman,

Magnus53 doesn't want EVERYTHING with test in it. Your solution will count tester and tester1 along with test and test1. Magnus53 said only test and test1, etc is wanted, not tester, tester1, etc.

-SQLBill

Posting advice: FAQ481-4875

 

[Magnus53]

Thanks for the replies everyone. I did manage to figure it out. I gave SQLBill the star because he put me on the right track.


Thanks again.