Know if theres a phone number in a string
Know if theres a phone number in a string
I have a table with a column called 'description' which is the description of an ad, and i want to know how many ads include the seller phone number in the description.
for example:
'car for sale. Toyota Corolla. Call 0759485102'
(all phone numbers have 10 digits)
how can i query that? im using mysql in datagrip
Welcome to SO! Please provide some sample data.
– SoConfused
Aug 23 at 18:13
LIKE operator
– JohnyL
Aug 23 at 18:13
idownvotedbecau.se/noattempt
– dfundako
Aug 23 at 18:16
Tag the DBMS (i.e.
MySQL, SQL Server, etc..) that you are using.– Yogesh Sharma
Aug 23 at 18:22
MySQL
SQL Server
2 Answers
2
Most databases support regular expressions -- at least sufficiently to start to answer this question. You can look for a 10-digit string. There is no way to know if this is really a telephone number (unless you have that somewhere else).
For instance, in MySQL, you can do:
select count(*) as num_with_10digits
from t;
where description regexp '[0-9]10';
The where clause differs by database, but the idea would be the same in most databases.
where
[42704][500310] [Amazon](500310) Invalid operation: type "regexp" does not exist; java.lang.RuntimeException: com.amazon.support.exceptions.ErrorException: [Amazon](500310) Invalid operation: type "regexp" does not exist;
– Luli Ferreira
Aug 23 at 18:47
You need to be specific about which database you're using so we don't have to guess. Each one often has wildly different methods for doing similar things. Is this MySQL?
– tadman
Aug 23 at 18:50
yes MySQL in datagrip
– Luli Ferreira
Aug 23 at 18:51
@LuliFerreira . . . I don't think you are using MySQL. You should use the regular expression matcher for your data.
regexp is quite explicitly supported by MySQL (dev.mysql.com/doc/refman/5.6/en/regexp.html).– Gordon Linoff
Aug 24 at 1:40
regexp
You can use like predicate :
like
select count(*)
from table t
where description like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]';
LIKE does not support regular expressions in standard SQL
– a_horse_with_no_name
Aug 23 at 18:21
This won't work without
RLIKE in MySQL.– tadman
Aug 23 at 18:49
RLIKE
doesn't work, it brings 0 results =(
– Luli Ferreira
Aug 23 at 19:22
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Please tag the question with the database you are using.
– Gordon Linoff
Aug 23 at 18:12