Exact Match using Like query [Resolved]

Posted by Alok under Design Pattern & Practices on 10/30/2012 | Points: 10 | Views : 1622 | Status : [Member] | Replies : 6
i have a table like this


id proid catid
1 1 1,2
2 2 2,3
3 3 1,3
4 4 2,5
5 5 3,5
6 6 11,2



i want to get proid which include catid 1

i write query as

select proid from e2 where catid like '%'+ '1'+'%'

it gives me o/p as

1
3
6

here we get 6 also ,,which i don'need

i need only proid
1
3
not 6 coz it is 11 not 1

how to write query to get exact match

Thanks & Regards,
Alokssm



Responses

Posted by: Vasanthmvp on: 10/30/2012 [Member] Starter | Points: 50

Up
0
Down

Resolved
Hi,
if we use
1. like '%1%' --> it checks if any part of string has 1 or not
2. like '1%' --> it checks if the string starts with 1 so here 11,2,1, also comes
3. like '%,1,%' --> ,1, anything.
So, better use like '%,1,%' --> this means only 1. since "," is included to separate.. it takes the whole part (,1,) and it is only 1.
Note: If the string is 12,2,3,1 --> Here ending with 1 so specify a or condition with like '%,1' that means string ending with 1.
In the same way, if starts with 1 like '1,%' or if the string is only 1 then '1'
Totally:
 SELECT proid FROM e2 WHERE catid LIKE '%,1,%' OR catid LIKE '1,%' OR catid LIKE '%,1' OR catid = '1' .

I think there might be a more simple query than this.

Regards,

Awesome Coding !! :)

Alok, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sourabh07 on: 10/30/2012 [Member] Starter | Points: 25

Up
0
Down
hi Alokssm

please try this.....query as i have tried and the output will come as you required....

select proid from e2 where catid like '1,%'


Output:


proid
-----------
1
3

Sourabh07

Alok, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vivekjj on: 10/30/2012 [Member] Starter | Points: 25

Up
0
Down
select proid from e2 where catid like '1,%' it will execute suppose if catid contains 1 instead of 1,3 means it not execute that records

Alok, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sourabh07 on: 10/30/2012 [Member] Starter | Points: 25

Up
0
Down
hi Alokssm

i thought it is fixed that we have to find out after comma....but it is not so....

Now, try this query instead.

select proid from e2 where catid not like '11%' and catid like '1%'


Output:
proid
-----------
1
3



Sourabh07

Alok, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sourabh07 on: 10/30/2012 [Member] Starter | Points: 25

Up
0
Down
hi Alokssm,

the following query also helpfull in finding the exact result........

Query:

select proid from e2 where catid like '1[^1]%'


Output:

proid
-----------
1
3


Sourabh07

Alok, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Alok on: 10/30/2012 [Member] Starter | Points: 25

Up
0
Down
Thanks Vasanthmvp,

You provide the best soluition and my query runs successfully..



Thanks & Regards,
Alokssm

Alok, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response