Looking For Phone Number Containing A Minus, Like "123-456789"

I have the following problem. SELECT .... FROM .... WHERE 'Phonenumber' LIKE '123456789' AND '*****' IS NOT NULL So my situation is that I read these numbers out of a text fil

Solution 1:

You could just use the REPLACE function to strip out the dashes:

SELECT ...
FROM ...
WHERE REPLACE("Phonenumber", '-', '') LIKE'123456789'AND ...

Alternatively process the strings from your text file and insert the dash between the fourth and fifth numbers, then use those for your SQL query.

Disclaimer: I'm not familiar with PostgreSQL syntax, so the above query may not be exactly correct (I took my cue from the SQL in the question).

Solution 2:

To get those rows where the dash is after the fourth digit and is followed by five digits:

where"Phonenumber" ~ '[0-9]{4}-[0-9]{5}'

To get those rows where the - is anywhere in the middle:

where"Phonenumber" ~ '[0-9]+-[0-9]+'

(so at least one digit, then the dash, then at least one more digit)

Solution 3:

To replace one or more single characters, translate() is generally faster (and shorter for multiple replacements) than replace() (which can replace whole strings).

...
WHEREtranslate("Phonenumber", '-', '') ='123456789'

It is also pointless to use LIKE without wildcards. Replace with a simple =.

If pattern does not contain percent signs or underscores, then the pattern only represents the string itself; in that case LIKE acts like the equals operator.

To make this fast with bigger tables, create a matching functional index:

CREATE INDEX tbl_phone_idx ON tbl (translate("Phonenumber", '-', '')

Test performance with EXPLAIN ANALYZE.