quick question on pattern matching

Hi folks,
I'm using Teradata SQL asistant and I was able to do this:
SELECT ACC_SHT_NM
FROM Accounts
WHERE ACC_SHT_NM LIKE ANY ('%LNA', '%PLNB')
Rather than having one wildcard clause with LIKE, then OR one after the othe
r
I can't seem to make this work in SQL server 2000
Is it possible to use some kind of list of wildcards with only one LIKE and
not loads of ORs but also followed by a comma separated list of wildcard
criteria as you would have with an IN list?
Regards and thanks in advance,
CharlesAInsert the values in a table, then join your table to this new table like so
:
select <column list>
from accounts
inner join <new table>
on accounts.ACC_SHT_NM LIKE <new
table>.<column>
or:
...
on accounts.ACC_SHT_NM LIKE '%' + <new
table>.<column>
(depending on how you plan to insert the search strings)
ML
http://milambda.blogspot.com/|||Thanks ML!
Regards
CharlesA|||So, did it work as expected?
ML
http://milambda.blogspot.com/|||it worked like a dream ML (I used method 1 with the % embedded in the string
inside the criteria table), so it's obviously evaluated at run time by the
LIKE
Thanks again
CharlesA|||Opposed to the "wildcard-string" pettern the "string-wildcard" pattern can
use indexes. If the need ever arises. :)
ML
http://milambda.blogspot.com/|||didn't get that last post at all :-)
any chance of a bit more elaboration?
Regards
CharlesA|||When the LIKE operator is used the query optimizer can only find use of an
index on the column if the pattern starts with value, not with a wildcard
character.
This enables the use of an index:
<column> LIKE 'string%'
This does not:
<column> LIKE '%string'
There is a trick: you can add a computed column with the following expressio
n:
ReverseColumn as reverse(column)
Of course you'd have to reverse the search tring as well. But - as I said
before - only if performance starts being a pain in the ***.
ML
http://milambda.blogspot.com/|||blimey that's much clearer!
since performance is fine though I'll stick to the easier method 1
Cheers
CharlesA|||Good on ya, mate! Kushdy.
ML
http://milambda.blogspot.com/