asked on

SQL - find accents in specific column

Hello all

I'm trying to build an query that will tell me if i have accents in some columns from my oracle table.

Ex: If for example in the column NAME i have the value: TEST * 1 and that the accents that i want to filter is * then it will flag it.

all the accents that i want to find are:

`,<,>,^,|,[,],*,é,É,Ç,ç,À,à,~,@, %, #,À,Á,Â,Ã,Ä,Å,Ç,È,É,Ê,Ë,Ì,Í,Î,Ï,Ð,Ñ,Ò,Ó,Ô,Õ,Ö,Ù,Ú,Û,Ü,Ý,à,á,â,ã,ä,å,ç,è,é,ê,ë,ì,í,î,ï,ð,ñ,ò,ó,ô,õ,ö,ù,ú,û,ü,ý,ÿ,/,!,@,#,$,%,?,*,(,),+

Open in new window

The column that i want to find these accents are in column:
NAME
ADDR1
CITY
ZIP

If there is an accents, it would say NAME_TO_FIX in a column NAME_VALIDATION.

Is that possible?

How can i do that?

Thanks again for your help

This is the SQL i have for now:

SELECT 
    NAME,
    ADDR1,
    CITY,
    STATE,
    COUNTRY,
    ZIP,
    CONTACT,
    PHONE,
    PHONE_SPD,
    FAX,
    CASE
        WHEN NAME = (all accents) THEN 'NAME_TO_FIX'
        ELSE ''
        END  NAME_VALIDATION,   

FROM 
    LOCATION

Open in new window

Sean Stuber

SELECT name,
addr1,
city,
state,
country,
zip,
contact,
phone,
phone_spd,
fax,
CASE
WHEN EXISTS
(SELECT NULL
FROM (SELECT SUBSTR(
'`,<,>,^,|,[,],*,é,É,Ç,ç,À,à,~,@, %, #,À,Á,Â,Ã,Ä,Å,Ç,È,É,Ê,Ë,Ì,Í,Î,Ï,Ð,Ñ,Ò,Ó,Ô,Õ,Ö,Ù,Ú,Û,Ü,Ý,à,á,â,ã,ä,å,ç,è,é,ê,ë,ì,í,î,ï,ð,ñ,ò,ó,ô,õ,ö,ù,ú,û,ü,ý,ÿ,/,!,@,#,$,%,?,*,(,),+',
2 * (LEVEL - 1) + 1,
1
)
c
FROM DUAL
CONNECT BY LEVEL <= 85)
WHERE INSTR(name, c) > 0)
THEN
'NAME_TO_FIX'
ELSE
''
END
name_validation
FROM location;

Wilder1626

ASKER

Hello sdstuber

In the result, i'm having name to fix even if i don't have accents

Do you know why?

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Wilder1626

ASKER

Ho! this is so good.

Thanks again for your help

slightwv (䄆 Netminder)

Even though this is already closed I wanted to post this.

You mentioned 9i in your tag. If you were in 10g or above, regexp might be a better option.

drop table tab1 purge;
create table tab1(name varchar2(20));

insert into tab1 values('No fix');
insert into tab1 values('test*1');
insert into tab1 values('abcdef*1');
commit;

SELECT name,
case when
length(replace(name,'a','')) != length(replace(translate(name,
	'`<>^|[]*éÉÇçÀà~@%#ÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåçèéêëìíîïðñòóôõöùúûüýÿ/!@#$%?*()+',
	'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),'a',''))
then 'Fix' end fixit
FROM tab1
/

Open in new window

Sean Stuber

10g regexp is easy but you have to do a little special handling for the right bracket ']'

the expression might look something like this...

SELECT name,
addr1,
city,
state,
country,
zip,
contact,
phone,
phone_spd,
fax,
CASE
WHEN regexp_like(name,']|[`<>^|[*éÉÇçÀà~@%#ÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåçèéêëìíîïðñòóôõöùúûüýÿ/!@#$%?*()+]')
THEN
'NAME_TO_FIX'
ELSE
''
END
name_validation
FROM location;

Wilder1626

ASKER

Thanks for the information.

This is good to know.

Sean Stuber

slightly more compact version of the TRANSLATE method

DECODE(
name,
TRANSLATE(
name,
'a`<>^|[]*éÉÇçÀà~@%#ÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåçèéêëìíîïðñòóôõöùúûüýÿ/!@#$%?*()+',
'a'), NULL,'Fix')

PortletPaul

for the performance curious

3 tests, same table (1152670 rows), same field containing(I18N) labels

NLS_RDBMS_VERSION 11.2.0.2.0

#1 using DUAL and CONNECT BY LEVEL and INSTR
 -------------------------------------------------------------------------------------------------- 
 | Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     | 
 -------------------------------------------------------------------------------------------------- 
 |   0 | SELECT STATEMENT              |                  |  1144K|  1165M|  4062   (1)| 00:00:49 | 
 |*  1 |  VIEW                         |                  |     1 |     3 |     2   (0)| 00:00:01 | 
 |*  2 |   CONNECT BY WITHOUT FILTERING|                  |       |       |            |          | 
 |   3 |    FAST DUAL                  |                  |     1 |       |     2   (0)| 00:00:01 | 
 |*  4 |  VIEW                         |                  |  1144K|  1165M|  4062   (1)| 00:00:49 | 
 |   5 |   TABLE ACCESS FULL           | <<removed>>      |  1144K|    29M|  4062   (1)| 00:00:49 | 
 -------------------------------------------------------------------------------------------------- 

 
#2 using REGEXP_LIKE
 --------------------------------------------------------------------------------------      
 | Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |      
 --------------------------------------------------------------------------------------      
 |   0 | SELECT STATEMENT  |                  | 11446 |   301K|  4120   (3)| 00:00:50 |      
 |*  1 |  TABLE ACCESS FULL| <<removed>>      | 11446 |   301K|  4120   (3)| 00:00:50 |      
 --------------------------------------------------------------------------------------    

 #3 DECODE
 -------------------------------------------------------------------------------------- 
 | Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     | 
 -------------------------------------------------------------------------------------- 
 |   0 | SELECT STATEMENT  |                  | 11446 |   301K|  4120   (3)| 00:00:50 | 
 |*  1 |  TABLE ACCESS FULL| <<removed>>      | 11446 |   301K|  4120   (3)| 00:00:50 | 
 -------------------------------------------------------------------------------------- 

not a lot of difference but #1 won
 
ps: this is a "starved VM" generally noted as slow :-(

Open in new window

Wilder1626

ASKER

I have tried this one so far and it is very good also

SELECT name,
       addr1,
       city,
       state,
       country,
       zip,
       contact,
       phone,
       phone_spd,
       fax,
       CASE
           WHEN  regexp_like(name,']|[`<>^|[*éÉÇçÀà~@%#ÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåçèéêëìíîïðñòóôõöùúûüýÿ/!@#$%?*()+]')
           THEN
               'NAME_TO_FIX'
           ELSE
               ''
       END
           name_validation
  FROM location;

Open in new window

I'm looking also at the others

slightwv (䄆 Netminder)

The tag to this question says 9i. I 'm pretty sure regexp_like didn't exist in 9i.

If you aren't running 9i then there might be additional options. What version are you running (please provide all 4 numbers)?