Like with right value with wildcard percent

Question

I need to search for countries ids and I ran into a problem, for example, let's take the country "Bolivia".

The Country table has a "CountryName" and "Alternative Names"

For the example of "Bolivia".

IdCountry  Name                             Code  AlternateNames
---------- -------------------------------- ----- ---------------------------
29         Bolivia, Plurinational State Of  BO    Bolivia, Bolivien, Bolivie

This is the query I've created:

SELECT cou.CountryID 
FROM Doppler2011.dbo.Country cou 
WHERE (cou.AlternateNames IS NOT NULL AND 'Bolivia' like '%'+cou.AlternateNames+'%') 
        OR 'Bolivia' like cou.Name

As you see, I need the cou.AlternateNames in the Right side of the like to match the name of the country with some of the alternatives names, for the countries which the name is the same as the cou.Name, the query works, but the problem is with those that have to match with AlternateNames

Can anyone help me?

Thanks!

Are the `AlternateNames` stored together as a column value? – swasheck Mar 08 '13 at 17:49 — swasheck, Mar 08 '13 at 17:49

Ateszki · Accepted Answer · 2013-03-08T17:45:51.563

3

You should have the wilcards on the other term like this

SELECT cou.CountryID 
        FROM Doppler2011.dbo.Country cou 
        WHERE cou.AlternateName like '%Bolivia%' OR cou.Name like 'Bolivia'

edited Mar 08 '13 at 17:45

answered Mar 08 '13 at 17:39

Ateszki

2,243
1
16
13

You're right, I dont know why this query confuse me so much... thanks! – Mariano G Mar 08 '13 at 17:43
Aaron Bertrand you are right, i'll edit. Mariano you are welcome. – Ateszki Mar 08 '13 at 17:45
I don't see how this would work. In the example the AlternateName is one of (Bolivia, Bolivien, Bolivie). `like '%Bolivia%'` will only ever match the first member of the set. It's very likely, though, that I'm misreading the question. – swasheck Mar 08 '13 at 17:46
... that is unless it's a comma-separated list stored in one column. – swasheck Mar 08 '13 at 17:48
I think is a coma separated string stored in the AlternateName column – Ateszki Mar 08 '13 at 17:48
1

If it is a comma-separated list then it should be `',' + cou.AlternateName + ',' LIKE '%,Bolivia,%'` - this way you only match the full word. – Aaron Bertrand Mar 08 '13 at 18:00
@Swasheck I assumed they meant `LIKE '%Bolivi%'` – Aaron Bertrand Mar 08 '13 at 18:01
Thanks all, I found the problem, the value "Bolivia" is from other table, and in the other table is set as char(30), so it was "Bolivia_____"(with 30 chars) so the comparation always return false. but thanks to Ateszki and a lot of test, I could fix the query – Mariano G Mar 08 '13 at 19:07

score 1 · Answer 2 · answered Mar 08 '13 at 17:42

1

your where clause is basically backwards. you normally say:

COLUMN_NAME like '%string_to_match%'

answered Mar 08 '13 at 17:42

Ramy

20,541
41
103
153

score 1 · Answer 3 · edited May 23 '17 at 11:45

Depending on your actual structure here are some thoughts.

If AlternateName is stored as a comma-separated list on one column value, and Name is always the first value of the list in AlternateName then you can use Ateski's answer

If it's not the first item in the list, then you'd need to follow @AaronBertrand's advice in his comment:

SELECT cou.CountryID 
    FROM Doppler2011.dbo.Country cou 
    WHERE ',' + cou.AlternateName + ',' LIKE '%,Bolivia,%' 
      OR cou.Name ='Bolivia';

Finally, if you could re-work your structure a bit so that you have a list of possible country names, you could come up with something like this:

http://sqlfiddle.com/#!3/6d3c8/1

Just an alternate perspective. Ateski's solution is perfectly valid and good.

Like with right value with wildcard percent

3 Answers3