Using ANY operator together with string functions

Question

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?

You should not use functions on columns in the WHERE clause. — Kermit
– Kermit, Commented Sep 11, 2012 at 15:20
Note that both the LIKE operator and UDFs aren't great for performance. Would be useful if the city was normalised out of the address. — Jodrell
– Jodrell, Commented Sep 11, 2012 at 15:31

bendataclear · Accepted Answer · 2012-09-11 16:00:51Z

1

Have you tried a JOIN?

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

Not sure about performance however...

answered Sep 11, 2012 at 16:00

bendataclear

3,8574 gold badges34 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jodrell · Accepted Answer · 2012-09-11 15:25:07Z

0

How about,

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

answered Sep 11, 2012 at 15:25

Jodrell

35.9k6 gold badges94 silver badges131 bronze badges

Comments

TheTechGuy · Accepted Answer · 2012-09-11 15:53:09Z

0

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.

edited Sep 11, 2012 at 15:53

answered Sep 11, 2012 at 15:20

TheTechGuy

17.5k18 gold badges120 silver badges144 bronze badges

2 Comments

RedSoft Over a year ago

Not, the IN operator finds exact matchings, but I want to find a substring matching

TheTechGuy Over a year ago

updated for substring problem. It is part of the solution though, I did not get your whole problem.

Collectives™ on Stack Overflow

Using ANY operator together with string functions

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related