[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SQL - find accents in specific column

Posted on 2013-02-06
11
Medium Priority
?
906 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 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

650 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