SQL "LIKE" query with multiple condition

Question

0.00/5 (No votes)

See more:

Hi,

i want to query on 2 lists finding in column 'A1' from Table 'A' with the 2 columns viz. B1, B2 in Table 'B'. Query should be using "LIKE" operator where Column A1 like ('%B1%') OR Column A1 like ('%B2%').

current result gives me 2 likes for single record, which is correct in its place because Column matched from multiple rows.

Here is i want...
If column A1 matched from B1 OR B2 then should skip looking for further matches. it should not look in other rows of table B. Also i want to mark that the column matched with B1 or B2.

Thanks

Pinank Shah

Posted 29-Apr-13 23:01pm

Pinank_CD

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Aarti Meswania · Answer 1 · 2013-04-29T23:47:00

It will not stop searching if one match found but you can filter records so, finally you will get result you want
Try this...

SQL

with tbl1 as
        (
            select 'Abc' as A1
            union all
            select 'xyz' as A1
        )
select * from
(
    select Row_Number() over(partition by A1 order by A1) SrNo,* from tbl1
    left join
        (
            select 'b' as B1, 'A' as B2
            union all
            select 'a' as B1, 'x' as B2
        ) as tbl2 on A1 like '%' + B1 + '%' or A1 like '%' + B2 + '%'
) as ftbl where srno=1

Happy Coding!
:)

CHill60 · Answer 2 · 2013-04-30T00:00:00

I'll walk through this solution bit by bit ...

Quote:
Query should be using "LIKE" operator where Column A1 like ('%B1%') OR Column A1 like ('%B2%')

.
Could be solved with

SELECT * FROM A INNER JOIN B ON A1 LIKE '%' + B2 + '%' OR A1 LIKE '%' + B1 + '%'

Quote:
If column A1 matched from B1 OR B2 then should skip looking for further matches

Use something like

SELECT TOP 1 * FROM A ...

which will stop once it has found a single matching row. Note that you can use this in conjunction with ORDER BY ... to refine it's behaviour.

Quote:
Also i want to mark that the column matched with B1 or B2

.I'm going to identify which column we used (B1 or B2) using a CASE statement like this

CASE WHEN CHARINDEX(B2, A1) > 0 THEN 'B2' ELSE 'B1' END AS WHICH

To pull of this together we're first going to identify the single matching row on table A with a sub-query and I'm going to call the results of that sub-query TEMPRES, then we're going to join A to TEMPRES to update the relevent row.

In the full example below I've assumed that you have some way of uniquely identifying each row in table A (this is usually a very good idea - have a look at the IDENTITY(s,i) property for a column). In my example I've called this ID

SQL

UPDATE A
SET A1 = TEMPRES.WHICH
FROM A
INNER JOIN
    (SELECT TOP 1 A.ID, CASE WHEN CHARINDEX(B2, A1) > 0 THEN 'B2' ELSE 'B1' END AS WHICH
        FROM A INNER JOIN B ON A1 LIKE '%' + B2 + '%' OR A1 LIKE '%' + B1 + '%' ) TEMPRES ON A.ID=TEMPRES.ID

Finally, you might find these references helpful
http://www.akadia.com/services/sqlsrv_subqueries.html[^]
http://msdn.microsoft.com/en-us/library/ms186775.aspx[^]

[Edit - for some reason I started using column A2 instead of A1 ... corrected the sql]

SQL "LIKE" query with multiple condition

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0