SQL Only return text that contains numbers

Question

I have a .db file that contains the contacts list from an android phone. I have joined two tables to display the mobile phone number next to the contact. But I am trying to display the records that only contain the mobile phone number. But when I run the SQL statement it does not return a table. You need to keep in mind some of the saved phone numbers begin with +44 and the others 0.

This is my SQL statement:

select contacts.display_name, data.data1 as Phone_Number,
    contacts.times_contacted, contacts.last_time_contacted 
from contacts, data 
where contacts._id = data.raw_contact_id and data.data1 like '%[0-9]%'

Any help would be appreciated.

What do you mean "it does not return a table"? What does it return if not a table? — Jonathan Wood, Dec 19 '12 at 22:44
nothing is returned, the data returned box is empty. Where as if I remove the 'and data.data1 like '%[0-9%' it returns the table fine, just not how I want it — Patrick Dibb, Dec 19 '12 at 22:47
Yes it is SQLite and i am using the SQLite Database Browser to view it — Patrick Dibb, Dec 19 '12 at 22:50

Dracs · Answer 1 · 2012-12-19T23:42:29.570

0

The keyword for using a regular expression in SQL is REGEXP. The following query should show any row where 'data.data1' contains a number.

SELECT contacts.display_name, data.data1 AS Phone_Number,
    contacts.times_contacted, contacts.last_time_contacted 
FROM contacts, data 
WHERE contacts._id = data.raw_contact_id AND data.data1 REGEXP "[0-9]"

Edit: As a said it my comment. SQLite doesn't seem to support regular expressions by default. It requires the user create a function called regexp() to define how it should be done. It doesn't seem that the program your using doesn't create the function for you.

SQLiteSpy Seems is another GUI database manager which does add support for using regular expressions.

edited Dec 19 '12 at 23:42

answered Dec 19 '12 at 23:02

Dracs

437
4
20

@PatrickDibb Just had a bit more of a look and it seems regular expressions isn't supported in SQLite by default. This question has some answers discussing how to create the function. http://stackoverflow.com/questions/5071601/how-do-i-use-regex-in-a-sqlite-query – Dracs Dec 19 '12 at 23:18
Thanks for your reply but I have been playing around with it and I have narrowed it down to the square brackets. For example if I use the line "and data.data1 LIKE '%0%'" it works, but this isn't what I want because not all mobile numbers include 0 – Patrick Dibb Dec 19 '12 at 23:34

score 0 · Accepted Answer · answered Dec 19 '12 at 23:31

0

SELECT cc.display_name, dd.data1 as Phone_Number
    , cc.times_contacted, cc.last_time_contacted 
FROM contacts cc
JOIN data dd ON cc._id = dd.raw_contact_id
WHERE (dd.data1 like '+44[0-9]%'
       OR dd.data1 like '0[0-9]%'
       )
     ;

NOTE: I transformed into JOIN syntax, which is easier to read.

answered Dec 19 '12 at 23:31

wildplasser

43,142
8
66
109

Thanks for your reply but the same error still occurs where nothing shows in the results area. But if I remove the square brackets and replace them with 7 it returns everything fine. This is partly right but not all numbers saved on the phone start with 07 or +447. – Patrick Dibb Dec 19 '12 at 23:45
Well, you could always add an extra OR dd.data1 LIKE 'xxx%' part, with xxx being the prefix you want. – wildplasser Dec 19 '12 at 23:56
I did think of that but what if the phone has many different numbers from different countries, that would require loads of OR functions. Just struggling to understand why the square brackets do not work – Patrick Dibb Dec 20 '12 at 00:07
1

@PatrickDibb the square brackets are a regular expression character class. The LIKE operator doesn't support them AFAIK. – Dracs Dec 20 '12 at 05:24

score 0 · Answer 3 · answered Dec 20 '12 at 17:20

What exactly are you trying to accomplish with data.data1 like '%[0-9]%'? Do you only want to show records where the field data.data1 contains one or more digits?

I see two ways to do that:

(data.data1 LIKE '%0%' OR data.data1 LIKE '%1%' OR data.data1 LIKE '%2%' OR data.data1 LIKE '%3%' OR data.data1 LIKE '%4%' OR data.data1 LIKE '%5%' OR data.data1 LIKE '%6%' OR data.data1 LIKE '%7%' OR data.data1 LIKE '%8%' OR data.data1 LIKE '%9%'), which is fugly and rather slow;
(data.data1 REGEXP '[0-9]+'). However, since the default distributions of SQLite do not include a built-in RegEx engine, you’ll have to add that function yourself (or use a library or utility which does provide one).

Otherwise, you’ll have to perform the filtering by some other means than using SQL.

SQL Only return text that contains numbers

3 Answers3