How to find continuous string or number without spaces?

Question

In abc table I have phone number field.

In that field I have records like:

123456789
123 456 789
12345 5678
787878787
144444444

I want to search only records which not have any space so the query would give result:

123456789
787878787
144444444

So what should be query?

possible duplicate of [SQL Query Where Field DOES NOT Contain $x](http://stackoverflow.com/questions/232935/sql-query-where-field-does-not-contain-x) — totymedli, Aug 17 '13 at 09:50
From your comment below, where you say you also want to remove parentheses, it seems you want to extract all digits from the field. For example if you have `(123) 456-7890`. If that's the case, you might want to edit your question so that you do all those things in one place rather than solve the problem in small unrelated pieces. — Jeremy Smyth, Aug 17 '13 at 10:10
Given there are already several answers, *maybe* you should ask a *new* question describing properly your actual data format, and the expected output. — Sylvain Leroux, Aug 17 '13 at 10:36

score 7 · Answer 1 · edited Jun 07 '14 at 13:49

7

MySQL documentation for LIKE and NOT LIKE

SELECT * FROM abc WHERE phone_number NOT LIKE '% %';

This will show the output as:

phone_number
-----------
123456789
787878787
144444444

Here is the SQLFiddle for above query and table script

edited Jun 07 '14 at 13:49

haky_nash

answered Aug 17 '13 at 09:48

Thanks :) After this result how can i convert all records to (XXX) XXX XXXX to this format – Jackson Aug 17 '13 at 09:54
@AnkitShah you can did it by using phone_number length and concat operations – Naveen Kumar Alone Aug 17 '13 at 10:09
UPDATE phone SET CONCAT('(',SUBSTRING( phone FROM 1 FOR LENGTH( phone ) -6 ),')',' ',SUBSTRING( phone FROM 4 FOR LENGTH( phone ) -6 ) ,' ',SUBSTRING( phone FROM 7 FOR LENGTH( phone ) -6 )) WHERE phone IN ( SELECT phone FROM test WHERE phone NOT LIKE '% %' ) why this is not working? – Jackson Aug 17 '13 at 10:50
It's giving error that's why unable to run query in sqlfiddle.com – Jackson Aug 17 '13 at 11:45

score 3 · Answer 2 · answered Aug 17 '13 at 09:50

If you only want "digit only" values from an arbitrary string column, you might probably use MySQL regular expression:

select * from tbl where col_name REGEXP '^[0-9]+$'

If you are absolutely sure your columns only contains digits or spaces:

select * from abc where col_name not like '% %'

Or

select * from abc where LOCATE(' ', col_name) = 0

None of there are not index friendly thought...

score 0 · Answer 3 · answered Aug 17 '13 at 09:50

0

SELECT * FROM myTable WHERE col REGEXP '[0-9]+';

answered Aug 17 '13 at 09:50

Hanky Panky

3 Answers3