case when LIKE on columns from 2 different tables
-
hello,
need some help with the follow.
problem: A.CITY contains a single city per row (i.e. TORONTO), B.CITY contains multiple cities per row (i.e. TORONTO, TOKYO )
my statement below returns 0, when i want it to return a 1
not too sure how i can change my statement to be able to search B.CITY when it has multiple cities listed and separated by a " , "
please help
thankscase
when A.CITY LIKE B.CITY then 1 else 0
end as 'Count' -
You don't want to do it this way. You want to split the string in b and then join on that split, otherwise you'll get matches like 'York' and 'New York, London'. Search this site for DelimitedSplit8K.
The reason you're not getting any matches, is because you don't have any wildcards in your search string in which case LIKE is equivalent to =.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - drew.allen - Wednesday, October 25, 2017 1:48 PM
what's funny is i tried to wild card the column and that clearly didn't work '%B.CITY%' -- no errors, but no results either
but that's for reinforcing proper form - henryly237 - Wednesday, October 25, 2017 2:45 PM
I'm gonna agree with Drew that you want to split the string and then search against those results.
But at some point you'll also need to address your wildcard usage... if you used '%B.CITY%' it's actually looking for the string "B.CITY" with any combination of characters on either side... so if the value for A.CITY were something like... 'yayB.CITYyay', you'd get a 1...'%' + B.CITY + '%' would be a good start, but then you've got your match_expression and pattern order mixed up.
This would look for B.CITY inside A.CITY
A.CITY like '%' + B.CITY + '%'
This would look for A.CITY inside B.CITYB.CITY like '%' + A.CITY + '%'You should check the examples here...
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql - henryly237 - Wednesday, October 25, 2017 2:45 PM
Well, the right format for that would be '%' + B.CITY + '%', but that isn't going to work to start with because you're searching for A.CITY anywhere within B.CITY, so you'd need to use B.CITY LIKE '%' + A.CITY + '%', but again, that's still not going to work correctly, for the reasons Drew pointed out. Find Jeff Moden's article that has "Tally Oh" in the title, and download the code at the end for the inline table-valued function which will split the B.CITY string into it's individual cities as separate rows, and then you can match to the Item field coming out of that function.
Steve (aka sgmunson)
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply