sql query to get data having special characters from a column

Question

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.

Answer 1

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

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

Answer 2

Some options:

SQL Fiddle

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

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:

[[: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:

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