SQL query - column like column

myship
myship used Ask the Experts™
on
i have two tables adn i want to do something like this

where col1 like col2


unfortunately this doesn't work as intended.
so lets say col2 is 1000 and col1 is 1000, 1002
this will not return any result

what i would want to do is return that row since col2 (1000) is like col1 (1000, 1002)

thanks in advance
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2006
Commented:
perhaps you need to use wildcards around it %

do you want to compare the columns in the same row or different rows?

another way, you can query the table and add it as 2 aliases

select a.*
from mytable as a, mytable as b
where a.col1 like "%" + b.col2 + "%"

something like that anyway
Top Expert 2006

Commented:
mysql takes concat for string concatenation. which db are you actually using?

where a.col1 like concat('%'. b.col2, '%')


use single quotes, not double like I did earlier
Top Expert 2006

Commented:
oops, typo. should be , not . after first wildcard
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Author

Commented:
your query is invalid in syntax

Author

Commented:
i am using ms sql
Commented:
this worked :D, so its single quote and not double quotes

'%' + b.col2 + '%'

one problem left

since one of the columns is an int b.col2 and the other is a string a.col1 i get this error



Conversion failed when converting the varchar value '%' to data type int.

Author

Commented:
so how can i cast a column as a string
NerdsOfTechTechnology Scientist
Commented:
are you trying to obtain a match from the inside values of col1? if so, rockiroads suggestion for wildcards  will accidentally match 10001, 1000928, 3381000, 310028, vaules etc (that has 1000) in col1 basically.

lets add a prefix comma and a suffix comma to col1 list

so now the search list of col1 is: ',1000,1002,'
and the match is looking for: ',1000,'

therefore, try this instead:
select a.*
from mytable as a, mytable as b
where (',' + a.col1 + ',') like "%," + b.col2 + ",%"

Open in new window

NerdsOfTechTechnology Scientist

Commented:
solution:
select a.*
from mytable as a, mytable as b
where (',' + a.col1 + ',') like '%,' + b.col2 + ',%'

Open in new window

NerdsOfTechTechnology Scientist

Commented:
we kill two birds with one stone. We not only cast col1 and col2 to string via + ',' but we also isolate each number for the match.

Sincerely,
NerdsOfTech
Top Expert 2006

Commented:
sorry, was working on another question.
looks like you sorted it out with cast. I missed that bit in the question.
your all sorted now :)
Ralf KlattEmployee in Civil Service

Commented:
Hi,
... unfortunately I couldn't test ... but just by looking at the task I'd suggest to try the following:

Declare @a as numeric
Declare @b as nvarchar(255)
Set @a= 1002
Select @b = CAST(@a as nvarchar [(255)])


Best regards,
Raisor
NerdsOfTechTechnology Scientist

Commented:
optionally you can use CONCAT as well with this altered list search:
select a.*
from mytable as a, mytable as b
where CONCAT(',', a.col1, ',') like CONCAT('%,', b.col2, ',%')

Open in new window

Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Conversion failed when converting the varchar value '%' to data type int.

You need a CONVERT. Best to also remove any spaces so each number is properly perfectly sandwiched between two commas.

','+replace(a.col1,' ','') +',' LIKE '%,' + CONVERT(varchar(10), b.col2) + ',%'
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
guys: concat is mysql, this is sql server :)
Ralf KlattEmployee in Civil Service

Commented:
Hi guys,

... while I was still thinking ... you were already working ...


Best regards,
Raisor
Top Expert 2006

Commented:
cyberkiwi the zones listed are


SQL Server 2005, MySQL Server, SQL Server 2008


so I mentioned concat for use with mysql just in case it was mysql being used. I am not 100% sure which db is being used.
NerdsOfTechTechnology Scientist

Commented:
wildcards will accidentally match 10001, 1000928, 3381000, 310028, vaules etc (that has 1000) in col1 basically.

so thats why I added a prefix comma and a suffix comma to col1 list

so now the search list of col1 is: ',1000,1002,'
and the match is looking for: ',1000,'
match found!

if the list was '10001, 1002, 1021000' then altered to ',10001, 1002, 1021000,' no match would be found

no cast needed as the strings will cast automatically via concat() or + '{string}' syntax on both sides
select a.*
from mytable as a, mytable as b
where CONCAT(',', a.col1, ',') like CONCAT('%,', b.col2, ',%')

Open in new window

NerdsOfTechTechnology Scientist

Commented:
cyberwiki thanks for the sandwich change!
NerdsOfTechTechnology Scientist

Commented:
wait nevermind... we need the sandwich on the inside of % ;)
NerdsOfTechTechnology Scientist

Commented:
try from my original post including cyberwiki's trim suggestion and my sandwich:
select a.*
from mytable as a, mytable as b
where (',' + replace(a.col1,' ','') + ',') like '%,' + b.col2 + ',%'

Open in new window

NerdsOfTechTechnology Scientist

Commented:
cyberwiki do we need convert after concat'ing col2?
select a.*
from mytable as a, mytable as b
where (',' + replace(a.col1,' ','') + ',') 
like ('%,' + b.col2 + ',%')

Open in new window

NerdsOfTechTechnology Scientist

Commented:
if so this should work:
select a.*
from mytable as a, mytable as b
where (',' + replace(a.col1,' ','') + ',') 
like ('%,' + CONVERT(varchar(12), b.col2)+ ',%')

Open in new window

NerdsOfTechTechnology Scientist

Commented:
solution:
select a.*
from mytable as a, mytable as b
where (',' + replace(a.col1,' ','') + ',') 
like ('%,' + CONVERT(varchar(12), b.col2) + ',%')

Open in new window

Expert of the Quarter 2010
Expert of the Year 2010

Commented:
myship,

You listed in you question '1000, 1002'

Even with what is proposed here http:#a33921686
What you get is

',1000, 1002,' LIKE ',1000,'

And won't match.  And it won't work without CONVERT/CAST.

@rockiroads, it was clarified in http:#a33921642 that it is ms sql.

@nerdsoftech > cyberwiki do we need convert after concat'ing col2?
Yes. See http:#33921658
Top Expert 2006

Commented:
@rockiroads, it was clarified in http:#a33921642 that it is ms sql.

which was posted after my question and suggestion of concat
Top Expert 2006

Commented:
I do not know why objections was raised. Looking at the CSG thread, I did not realise the question was to be deleted.

Perhaps myship can confirm what did and didn't work and what solution is being used.
NerdsOfTechTechnology Scientist

Commented:
to argue against cyberwiki's 33921760 comment:

"the search result would be ..."

it would actually be:

',1000, 1002,' LIKE '%,1000,%'

thus a MATCH would be found

THEREFORE I recommend the following point split:

rockiroads: 33921577
NerdsOfTech: 33921689
CyberWiki: 33921707
NerdsOfTech: 33921751
CyberWiki: 33921760
NerdsOfTechTechnology Scientist

Commented:
primary solution @ 33921751 that combines many of cyberwikis suggestions -- point split as requested above in #33922053 stands
Top Expert 2006

Commented:
sorry, I disagree. I would rather myship make the recommendation. If that means rejecting whatever I posted by myship then fine. I will accept that.

I did see the closing comment that myship was going to use cast but the objection posted mentioned delete, which I don't know anything about.

Author

Commented:
thanks
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
N-o-Tech,

Your CSG question is still open so I suggest pursuing it.  The accepted answer is neither syntactically correct nor logically robust in any DBMS, not even the purported MySQL for that comment.

MySQL: does not allow concat via + symbol.
MSSQL: does not use double quotes for strings.

Just by the by
[
',1000, 1002,' LIKE '%,1000,%'
]
Well, how do you match 1002 then may I ask?
Top Expert 2006

Commented:
I mentioned using single quotes afterwards, I said concat was for mysql and asked what kind of db it is before the author confirmed the db and note the post after the accepted solution. I specifically said it was for mysql, not t-sql.

mysql takes concat for string concatenation. which db are you actually using?where a.col1 like concat('%'. b.col2, '%')use single quotes, not double like I did earlier



regarding the test results, that is a question for the author. I am sure the author must of done some testing before deciding what worked satisfactory.

Regarding this comment

Your CSG question is still open so I suggest pursuing it

I think you should pursue it if you are not happy

Like I said before, this statement means I am not fussed if my post is accepted or not.

I would rather myship make the recommendation. If that means rejecting whatever I posted by myship then fine. I will accept that.
NerdsOfTechTechnology Scientist

Commented:
cyberkiwi,

You don't see me going around calling you cyberfruit - I apologize for the cyberwiki typo earlier - call it dyslexia. I believe you are good at syntax and what you do. In fact, I utilized some of your pointers to obtain a solution that I thought would be test-worthy. So did rockiroads. However, I do believe that your attitude is downright silly at times.

Thanks for the laugh.

and to answer your question:
Q: Well, how do you match 1002 then may I ask?

A: if the id you are searching for in a list IS 1002 then LIKE '%,1002,%' will match an IDList such as

',9281, 1002,' or ',1001,1002,'

and LIKE '%,1001,%' will match an IDList such as ',1001,1002,'

id           idlist           id           idlist           zstr           zmatch
1001       1001, 1002       1001       1001, 1002       ,1001,1002,       %,1001,%
1002       9281, 1002       1001       1001, 1002       ,1001,1002,       %,1002,%
1002       9281, 1002       1002       9281, 1002       ,9281,1002,       %,1002,%

as you can see, the like '%,[id],%' will exactly match from an IDList that is csv (with no spaces) and has prefix and suffix commas. BTW you pointed out removing spaces.

Cheers!
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems Analyst
Top Expert 2007

Commented:
"However, I do believe that your attitude is downright silly at times."
Nooooooo, really ?

"cyberfruit "
Can I place an order for some ?
Top Expert 2006

Commented:
lol

cyberkiwi, we have to be professional here. sometimes we win some, sometimes we don't. there are times when I have seen better solutions not accepted, sometimes the wrong solutions. we can only go so far sometimes in explaining to the questioners the merits of different solutions.

But as always ee provides request for assistance link to handle queries.

regarding your comment "The accepted answer is neither syntactically correct nor logically robust in any DBMS, not even the purported MySQL for that comment."

If you feel that strongly about it, you have to click on request assistance yourself and not ask others to do so. I have no qualms if you do since you have genuine concerns.
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
@myship - I am hijacking your thread so please accept my apologies and stop reading now.  This has nothing to do with you or the question.  Thank you.

@experts,

In my 2nd comment, that statement explicitly references a particular solution.

[Even with what is proposed here http:#a33921686
What you get is
',1000, 1002,' LIKE ',1000,'
And won't match.  And it won't work without CONVERT/CAST.]

Yes a later solution works incorporating replace.  I have to claim weekend-itis when I read your comment http:#a33922053 the first time, because all I read was

[
it would actually be:
',1000, 1002,' LIKE '%,1000,%'
thus a MATCH would be found
]

Which (without the replace) would not have worked.  That was a direct argument (and it even included the word "argument" against my comment against http:#a33921686 - if we all understand) so I took the bait.  Reading it again, I would have retracted that portion of my 3rd comment.  In fact, I formally retract it.

---
I read a rule somewhere that you should not post in CSG threads that you don't own under thread of a ban, so I didn't.  Since there was already a CSG ticket open, I thought better of starting a new one, seeing that NerdsofTech has already opened 3 and had 2 promptly closed referring to the first.

Does that clear the air?

---
Nice of you to drop by DatabaseMX just to share your gem about how you feel about me.  That's really professional.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems Analyst
Top Expert 2007

Commented:
"Nice of you to drop by DatabaseMX just to share your gem about how you feel about me.  That's really professional."

Thank you mate. I thought so too.  Just out for a sunday drive and thought I would drop in for a beer. On my way, I passed a freeway off ramp sign that led to :

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26547907.html?cid=748#a33918264

mx
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
<offtopic>

NerdsofTech,

People call me cyber, ck, kiwi, wiki and all sorts of abbreviations.  I never take offence.  Over the past two days, I've grown a [bad!] habit of abbreviating people's names.  I was about to complete with n-o-t but that didn't look nice so I went with tech.  Didn't realize it still doesn't look good even with dashes.  That was not a deliberate attempt at a derogatory play on your name.

I sincerely apologize if that offended you.

Hope to see you around on better terms.

</offtopic>
NerdsOfTechTechnology Scientist

Commented:
<offtopic>
I should have presumed you didn't mean anything by it. *doh* my nickname here is almost impossible to abbreviate. I apologize too.

I am sure we will see each other around here again on EE.

Thanks again supreme one,
(you can call me anything you like, even Nerd)
</offtopic>
NerdsOfTechTechnology Scientist

Commented:
I recommend splitting even:

http:#33921751  100
http:#33921577  100
http:#33921689  100
http:#33921707  100
http:#33921760  100

Commented:
Question reopened and being closed os originally intended.

quomodo
Community Support Moderator

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial