如何获取SQL中不包含特定2个字符的所有字段?

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

我正在使用sqlplus，我想要提取SQL中除[0-9，'-'，'+'，'('，')'，' ']之外的所有包含任何字符的联系号?我试着这

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:

例子:

[phone]
[phone]
[phone]??
+abc
+651234

Expected output

预期的输出

[phone]??
+abc

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

由于我是第一次使用sql，所以无法组合这些条件，有人能帮我吗?

3 个解决方案

#1

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

你可能需要如下的东西，这里你把三个不喜欢的条件合并成一个，通过使用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 '%+%')

#2

Demo: http://rextester.com/PECYN79110

演示:http://rextester.com/PECYN79110

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

isnumeric确保其余的值都是0-9。替换我们希望允许有效的字符。

with cte (Voice) as (
 SELECT '[phone]' union all
 SELECT '[phone]' union all
 SELECT '[phone]??' union all
 SELECT '+abc' union all
 SELECT '+651234'union all
 SELECT '[phone]')

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.

由于字符串操作将不会很快;因此没有索引使用。但是，您可以使用where子句表达式创建一个计算列，然后您可以仅引用计算的列isValidVoice，其值为1或0。我们将性能成本放在数据保存上，而不是随后的查询。所以如果阅读性能是一个问题;但是保存性能可以稍等几分之一秒，然后您就可以有更好的性能。

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.

或者，您可以将where子句设置为传入语音的函数，如果它是基于规则的isValidPhoneNumber，则只需返回该函数。这样，函数可以在多个地方使用;或作为多列/表上的计算列;但这也会更慢。

#3

You can try this.

你可以试试这个。

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

Result:

结果:

VOICE
---------------
[phone]??
+abc