sql查询从列中获取具有特殊字符的数据

I want to get data from this column having all kind of special charaters.

我想从这个专栏中获取具有各种特殊字符的数据。

The special characters are as below:

特殊字符如下:

&   *   ,   .   :   ;   `   ~   ¿   Ä   Å   Ç   É   Ñ   Ö   Ü   ß   à   á    
â   ä   å   ç   è   é   ê   ë   ì   í   î   ï   ñ   ò   ó   ô   ö   ù   ú    
û   ü   ÿ   ƒ   α   

I am running the below query, but no result:

我正在运行以下查询,但没有结果:

select A.Street  
from ADRC a
where A.Street like not LIKE '%[^A-Za-z0-9, ]%'

the above result is fetched from

上面的结果取自

select A.Street  
from ADRC a

when I am running the like clause i m not getting results.

当我运行like子句时,我没有得到结果。

2 个解决方案

#1

In Oracle, you can use REGEXP_LIKE to fetch any record that contains at least one "special" character:

在Oracle中,您可以使用REGEXP_LIKE来获取包含至少一个“特殊”字符的任何记录:

select a.street
from adrc a
where REGEXP_LIKE (a.street, '[^A-Za-z0-9, ]');

#2

Some options:

SQL Fiddle

Oracle 11g R2 Schema Setup: Some test data with 1 character per row:

Oracle 11g R2架构设置:一些每行1个字符的测试数据:

CREATE TABLE table1 ( a ) AS
SELECT SUBSTR( value, LEVEL, 1 )
FROM   (
  SELECT '&*,.:;`~¿ÄÅÇÉÑÖÜßàáâäåçèéêëìíîïñòóôöùúûüÿƒα'
         || 'abcdefghijklmnopqrstuvwxyz'
         || 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
         || '0123456789' AS value
  FROM   DUAL
)
CONNECT BY LEVEL <= LENGTH( value );

Query 1:

Naively, if you want to just match specific characters then you can just enumerate all the characters you want to match in a regular expression:

天真地,如果你想只匹配特定的字符,那么你可以枚举你想要在正则表达式中匹配的所有字符:

'[&*,.:;`~¿ÄÅÇÉÑÖÜßàáâäåçèéêëìíîïñòóôöùúûüÿƒα]'

For example - this lists all the matched characters (aggregated into a single row for compactness):

例如 - 它列出了所有匹配的字符(为了紧凑而聚合成一行):

SELECT LISTAGG( a, '' ) WITHIN GROUP ( ORDER BY ROWNUM ) AS matches
FROM   table1
WHERE  REGEXP_LIKE( a, '[&*,.:;`~¿ÄÅÇÉÑÖÜßàáâäåçèéêëìíîïñòóôöùúûüÿƒα]' )

Results:

|                                     MATCHES |
|---------------------------------------------|
| &*,.:;`~¿ÄÅÇÉÑÖÜßàáâäåçèéêëìíîïñòóôöùúûüÿƒα |

Query 2:

If you know the characters you do not want to match then you can negate the pattern [^characters to not match] to find out whether there are any other characters:

如果你知道你不想匹配的字符,那么你可以否定模式[^字符不匹配]以找出是否还有其他字符:

For example:

SELECT LISTAGG( a, '' ) WITHIN GROUP ( ORDER BY ROWNUM ) AS matches
FROM   table1
WHERE  REGEXP_LIKE( a, '[^a-z0-9, ]', 'i' )

Results:

|                                    MATCHES |
|--------------------------------------------|
| &*.:;`~¿ÄÅÇÉÑÖÜßàáâäåçèéêëìíîïñòóôöùúûüÿƒα |

Query 3:

If you do not have a simple expression you can negate but, instead, want to match characters that are equivalent to base letters then you can use [=a=] to match a, à, á, â, ä or å and would give a regular expression like:

如果你没有一个简单的表达式,你可以否定,但是,想要匹配相当于基本字母的字符,那么你可以使用[= a =]来匹配a,à,á,â,ä或å并给出正则表达式如:

[[:punct:][=a=][=c=][=e=][=i=][=n=][=o=][=u=][=y=]α߃]

For example:

SELECT LISTAGG( a, '' ) WITHIN GROUP ( ORDER BY ROWNUM ) AS matches
FROM   table1
WHERE  REGEXP_LIKE( a, '[[:punct:][=a=][=c=][=e=][=i=][=n=][=o=][=u=][=y=]α߃]', 'i' )

Results:

|                                                     MATCHES |
|-------------------------------------------------------------|
| &*,.:;`~¿ÄÅÇÉÑÖÜßàáâäåçèéêëìíîïñòóôöùúûüÿƒαaceinouyACEINOUY |

Query 4:

But that also matches the base characters, so we could negate the previous match and use REGEXP_REPLACE to strip out the non-matching characters and then test for the existence of a non-base character:

但这也与基本字符匹配,因此我们可以否定前一个匹配并使用REGEXP_REPLACE去除不匹配的字符,然后测试是否存在非基本字符:

SELECT LISTAGG( a, '' ) WITHIN GROUP ( ORDER BY ROWNUM ) AS matches
FROM   table1
WHERE  REGEXP_LIKE(
         REGEXP_REPLACE(
           a,
           '[^[:punct:][=a=][=c=][=e=][=i=][=n=][=o=][=u=][=y=]α߃]'
         ),
         '[^a-z]',
         'i'
       )

Results:

|                                     MATCHES |
|---------------------------------------------|
| &*,.:;`~¿ÄÅÇÉÑÖÜßàáâäåçèéêëìíîïñòóôöùúûüÿƒα |