CODEWITHSUNDEEP
sqlsql-servert-sql
RedSoft
RedSoft

Reputation: 371

Using ANY operator together with string functions

I want to build a query like this:

SELECT * FROM T1 WHERE Charindex(ANY(SELECT City FROM Cities),T1ADDRESS)>0

As i understand, the ANY operator cannot stay as SQL functions argument. So, what is the alternative?

Suppose I want to write a UDF that returns 1 or 0 dependent on one input argument Address. How do I do this without for loop operator and without accessing to SELECT City FROM Cities array by index, as it can be done easily in the procedural languages?

Upvotes: 0

Views: 110

Answers (3)

bendataclear
bendataclear

Reputation: 3850

Have you tried a JOIN?

SELECT * 
FROM T1
INNER JOIN CITIES
    ON T1ADDRESS LIKE '%' + City + '%'

Not sure about performance however...

Upvotes: 1

TheTechGuy
TheTechGuy

Reputation: 17354

You might want to do something like this?

SELECT * FROM T1 WHERE Charindex in (SELECT '%'+City+'%' FROM Cities)

which address the ANY problem but not everything you asked for.

Upvotes: 0

Jodrell
Jodrell

Reputation: 35716

How about,

SELECT 
              * 
    FROM 
            T1 
    WHERE
        EXISTS
        (
            SELECT City FROM Cities WHERE T1.T1ADDRESS LIKE '%' + City + '%' 
        )

Upvotes: 0

Related Questions