Re: Regexp, replace ??? what do I need to use

[Sandra Rascheli]

What I am having trouble is how to see if a particular number is present in the result of this query, For example if I was searching for the number 123456 in the table where identification could be written with extra alphanumeric characters, like this,

id identification
1 45.236.566
2 V-5.683.377
3 V-1785555
4 E-123.456
5 83003332

I would like the query to give me the result that row 4, contains the record I am looking for. Here is the query you suggested:

SELECT identification, GROUP_CONCAT(SUBSTRING(identification, i, 1) ORDER BY i SEPARATOR '')
FROM clients
JOIN ints ON i BETWEEN 1 AND LENGTH(identification)
WHERE
SUBSTRING(identification, i, 1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
GROUP BY identification;

But the question is how, to select from this resulting set, the match for identification 123456. How should I modify this query to match this particular identification? Is it a subquery? Do I have to send the results from this query to a table and search there? I am not sure how to handle this. Comments will be aprreciated. Thanks.