Asteric in Query Search - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Asteric in Query Search

Author

Topic

nhaas
Yak Posting Veteran

90 Posts

Posted - 2007-02-05 : 19:03:22

I have converted a ACCESS database into MSSQL 2000. I want to do a search in the Settype colum and if it sees a * dont show it, but it does not seem to work. Everything that I have tried just resorts the results with the same amount of lines. This query below I just want to see everything with a Astric

Select TYPE, SETTYPE, LOC, KEY FROM TESIS2
WHERE SETTYPE LIKE '*%'
ORDER BY LOC

Numbers are always like *1234 or 1234
asteric is always the first character if it is there

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-02-05 : 19:38:44

if you want to see everything with an asterisk, you need another %:

WHERE SETTYPE LIKE '%*%'

this will match any value with an asterisk anywhere in the value.

www.elsasoft.org

nhaas
Yak Posting Veteran

90 Posts

Posted - 2007-02-06 : 12:59:17

Yes that works, I think that my problem is with the fact that I want to also have other criteria here is what I am trying to do

SELECT TYPE, SETTYPE, LOC, KEY0 FROM TESIS2 WHERE TYPE = 'CELL' or TYPE = 'CEL'
Select TYPE, SETTYPE, LOC, KEY0 FROM TESIS2 WHERE SETTYPE LIKE '%*%'

I want all types that match cell, cel, and have the * in place. My brain hasnt done to much SQL lattly.....they both work separatly but when I put them together - it seems that they dont work.

thank you

nhaas
Yak Posting Veteran

90 Posts

Posted - 2007-02-06 : 13:02:09

Also this does not work:
SELECT TYPE, SETTYPE, LOC, KEY0 FROM TESIS2 WHERE TYPE = 'CELL' or TYPE = 'CEL' OR TYPE = 'CDMA' AND SETTYPE LIKE '%*%'
it just returns everything that matches

OR

SELECT TYPE, SETTYPE, LOC, KEY0 FROM TESIS2 WHERE TYPE = 'CELL' or TYPE = 'CEL' OR TYPE = 'CDMA' = (SELECT SETTYPE FROM TESIS2 WHERE SETTYPE LIKE '%*%')
just gets an error

nhaas
Yak Posting Veteran

90 Posts

Posted - 2007-02-06 : 13:12:23

OK GOT it SELECT TYPE, SETTYPE, LOC, KEY0 FROM TESIS2 WHERE (TYPE = 'CELL' or TYPE = 'CEL' OR TYPE = 'CDMA') and SETTYPE LIKE '%*%' ORDER BY SETTYPE

I think my brain clicked finally - thinks SQL today

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-06 : 13:14:42

this?

Select TYPE, SETTYPE, LOC, KEY FROM TESIS2
WHERE SETTYPE not LIKE '*%'
ORDER BY LOC

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources