?
Solved

SQL - find accents in specific column

Posted on 2013-02-06
11
Medium Priority
?
925 Views
Last Modified: 2013-02-07
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

0
Comment
Question by:Wilder1626
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 38862096
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;
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 38862159
Hello sdstuber

In the result, i'm having name to fix even if i don't have accents
accent
Do you know why?
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 38862168
sorry, I didn't notice the extra spaces in your string, remove those



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 <= 84)
                      WHERE INSTR(name, c) > 0)
           THEN
               'NAME_TO_FIX'
           ELSE
               ''
       END
           name_validation
  FROM location;
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 38862210
Ho! this is so good.

Thanks again for your help
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38862244
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

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38862257
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;
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 38862263
Thanks for the information.

This is good to know.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38862270
slightly more compact version of the TRANSLATE method

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

Expert Comment

by:PortletPaul
ID: 38862745
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

0
 
LVL 11

Author Comment

by:Wilder1626
ID: 38864913
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
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38864933
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)?
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question