SQL query quick problem

[asantos]

Hi,
i have this table: ID (int),Name (string),Childs (string)

For example, this can be the data:
1,"Andres","27,43,12"
2,"Roberto","23,14,58"
3,"Sebastian","6,23,18"

I need to make a SQL query which grabs all records where the fields CHILDS holds a number 23 in its data. In this case, it should print ID 2 and 3.

How can i achieve this?

Thanks,
andres

[stajer]

SELECT id
FROM tableName
WHERE childs LIKE '%23%'

[LifeinAsia]

That will work, as long as there is no child that contains "123" or "223" or other combination with "23" in it.

[cmarshall]

A better (but more difficult) solution is REGEXP [dev.mysql.com]

[asantos]

cmarshall:
exactly thats the one. of course... i am having trouble by writing the regular expression in there. any ideas?

[cmarshall]

i am having trouble by writing the regular expression in there. any ideas?

Regex gives EVERYONE trouble. It is non-trivial to write them.

There's a good O'Reilly book, called "Mastering Regular Expressions [oreilly.com]," that I highly recommend.

This [zvon.org] is also a useful site.

[asantos]

well, the problem is that i cannot even do a basic regex query. what is wrong with this line?

SELECT * FROM table WHERE childs REGEX ("4")

[cmarshall]

I'm not sure, but I think the problem may be the parentheses. REGEXP is an operator, not a function.

And it's REGEXP, not REGEX.

[asantos]

My fault, it works now. Thanks.

[asantos]

I've come to this... but still problems when trying to retrieve the correct data. What is wrong?

SELECT * FROM blog WHERE stickers REGEXP "(,)?23{1}(,)?"

[coopster]

Looks like you may be running MySQL. FIND_IN_SET [webmasterworld.com] may be another option for you.

Related threads:
[webmasterworld.com...]
[webmasterworld.com...]
[webmasterworld.com...]

[asantos]

Thank you all for tips.
Both of this work fine:

SELECT *
FROM table
WHERE FIND_IN_SET( '23', childs )
//Showing rows 0 - 0 (1 total, Query took 0.0004 sec)

SELECT *
FROM table
WHERE childs REGEXP '[[:<:]]23[[:>:]]'
//Showing rows 0 - 0 (1 total, Query took 0.0004 sec)

As you can see, they also take the exact amount of execution time.

Andres

[asantos]

I still have a small doubt.
Is it the same execution time for a table of 10 records and for the same table with 1 million records as long as i limit the query like this?

SELECT *
FROM table
WHERE FIND_IN_SET( '23', childs )
ORDER BY RAND()
LIMIT 3

(they have to be random records.. but only 3 of them).