Search Exact Match

Databases
1

Hi everyone,

I am now facing a small issue related to querying a field to search for an exact match within a string. Here’s the picture:
Field Value: “1000, 1200, 1800”
Query: “… WHERE field LIKE ‘%XXX%’…”

The problem comes up when searching for say “200”, the result returns positive although it needs it to return negative since “1200” should not be regarded as “200”. How can this be done? I know the search query should be slightly different.

Thanks a lot in advance!

2

Isn’t it:

“… WHERE field REGEXP ‘^(XXX)’…”

3

i’ve said it before and it’s worth repeating… the reason it’s a bad idea to violate first normal form (which disallows multiple values stored in a single column) is because of the headaches you’re going to have querying the data

you could use the FIND_IN_SET function, it will get the correct results, but it requires a table scan, which means your query won’t scale, i.e. the more rows you have the slower it gets

4

Ok, so how do you suggest that I store the data, assume I need to save the 2G Network frequency bands for a mobile phone, that looks like “850, 900, 1800” and then run a filter to only display phones that support the “900” band? It currently saves the comma delimited data in a single field.

5

change this table layout –

phone1 850,900,1800
phone2 900,937
phone3 850,1200

to this –

phone1 850
phone1 900
phone1 1800
phone2 900
phone2 937
phone3 850
phone3 1200

same data, normalized

and now your query becomes trivial to write, as you are looking for a specific row

6

Thanks again, r937.

I like your suggested table layout, however it currently isn’t an option in my case, unfortuately. So I have to stick to the regular expression search as the best alternative that I discovered so far. According to some sources, I should be fine using the following:

SELECT * FROM table WHERE field REGEXP '[[:<:]]build[[:>:]]'

Performance wise, do you think this would be a significant slow down compared to your recommendation?

7

absolutely, yes it would be