CODEWITHSUNDEEP
sqlsql-serverdatabase
user6438693
user6438693

Reputation:

How to get all the fields which do not contain specific 2 characters in SQL?

I am using sqlplus and I want to extract all contact numbers which contains any character apart from [0-9, '-','+', '(',')',' '] in SQL? I tried this 

select VOICE from MERCHANT_MP_CONTACTS where (VOICE not like '%[0-9]%')
select VOICE from MERCHANT_MP_CONTACTS where (VOICE not like '%-%')
select VOICE from MERCHANT_MP_CONTACTS where (VOICE not like '%+%')..

Example:

Expected output

As i am using sql for the first time so i am not able to combine these conditions, Can anyone help me on this?

Upvotes: 0

Views: 259

Answers (3)

xQbert
xQbert

Reputation: 35323

Demo: http://rextester.com/PECYN79110

isnumeric ensures remaining values are all 0-9. replaces eliminate the characters we want to allow as valid.

with cte (Voice) as (
 SELECT '+65 8131 6259' union all
 SELECT '+64-21-126-0024' union all
 SELECT '+66 955940641??' union all
 SELECT '+abc' union all
 SELECT '+651234'union all
 SELECT '+1(555) 555-5555')

SELECT cte.*
FROM cte
WHERE isnumeric(replace(
                replace(
                replace(
                replace(
                replace(voice,' ','')  --Eliminate spaces
                             ,')','')  --Eliminate )
                             ,'(','')  --Eliminate (
                             ,'-','')  --Eliminate -
                             ,'+','')  --Eliminate +
               ) = 0                   --show only those not numeric remaining.

Will not be speedy due to string manipulation; thus no index use. However, you could create a computed column using the where clause expression then you could just reference the computed column isValidVoice with 1 or 0. We put the cost for performance on the data save instead of subsequent queries. So if read performance is a concern; but save performance can wait a fraction of a second more, then you could have better performance.

Alternatively you could make the where clause a function passing in voice and it simply returns if it's isValidPhoneNumber based on your rules. This way the function can be used in multiple places; or as a computed column on multiple column/tables; but that too would be slower.

Upvotes: 1

Serkan Arslan
Serkan Arslan

Reputation: 13393

You can try this.

select VOICE from MERCHANT_MP_CONTACTS WHERE (REPLACE(VOICE,' ','') like '%[^0-9+-]%')

Result:

VOICE
---------------
+66 955940641??
+abc

Upvotes: 0

Chetan_Vasudevan
Chetan_Vasudevan

Reputation: 2414

You may need something like below, here you take all your three not LIKE conditions and combine into a single one, by using UNION

select VOICE from MERCHANT_MP_CONTACTS where (VOICE not like '%[0-9]%')
union
select VOICE from MERCHANT_MP_CONTACTS where (VOICE not like '%-%')
union
select VOICE from MERCHANT_MP_CONTACTS where (VOICE not like '%+%')

Upvotes: 0

Related Questions