Solved

SQL - find accents in specific column

Posted on 2013-02-06
11
843 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 500 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 11

Author Closing Comment

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

Thanks again for your help
0
 
LVL 77

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 48

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 77

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to take different types of Oracle backups using RMAN.

733 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