CODEWITHSUNDEEP
mysqlsql
jmenezes
jmenezes

Reputation: 1926

Check if a country code exists in a cell

I'm trying to check if a country code exists in a cell country before a user can add anything to the database. So, if a country code did not exist, he would not be able to add anything.

I'm storing country codes as comma separated values. The column type is VARCHAR.

"id"    "rtype" "country"
"1"     "0"     "US,UK,SE,CA"
"2"     "1"     "US"
"3"     "2"     "UK"

I ran the following query, but this results in no rows. Where am going wrong here? The problem is, this query needs to run with comma separated values as well as single values.

select id, rtype from test where id=1 and country in('US')

Expected results

id | rType
 1 |   0

select id, rtype from test where id=2 and country in('US')

Expected results

id | rType
 2 |   0

Upvotes: 2

Views: 590

Answers (3)

neves
neves

Reputation: 39243

MySQL has a really nice Set Datatype. It is very efficient and can store till 64 distinct members of 255 values. I don't know if you could change your column type, but it would solve a lot of problems:

  1. your column would be indexable
  2. you wouldn't store duplicated values by mistake
  3. it would be stored very efficiently
  4. invalid values would be ignored (or error if in strict mode)
  5. trailing spaces are removed

You'd search using the FIND_IN_SET function.

Upvotes: 1

user2884957
user2884957

Reputation: 1

Since the country codes are the comma separated values , you can not directly use in . You can you like instead.

select id, rtype from test where country like '%US%'

Upvotes: 0

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

Try this out:

SELECT id, rtype FROM test
WHERE FIND_IN_SET('US', country) > 0

This is how the FIND_IN_SET(str,strlist) function works:

Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by “,” characters. [...] Returns 0 if str is not in strlist or if strlist is the empty string.

Upvotes: 3

Related Questions