Solved

SQL - find accents in specific column

Posted on 2013-02-06
11
822 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 73

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 73

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
 
LVL 11

Author Closing Comment

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

Thanks again for your help
0
 
LVL 76

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 73

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 73

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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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.​
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
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now