Home » SQL & PL/SQL » SQL & PL/SQL » Accented Vowels (oracle 10g,Windows Xp)
Accented Vowels [message #316544] Fri, 25 April 2008 11:17 Go to next message
ShridharV
Messages: 29
Registered: January 2008
Junior Member
Hi,

How to find the records that contain accented vowels like ,, etc. Like there is a column in the database which has some data containing Accented Vowels. Is there a way to find out?
Re: Accented Vowels [message #316549 is a reply to message #316544] Fri, 25 April 2008 12:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select id
from mytable
where translate(mycol,
                ''||translate(mycol,'a','a'),
                '')
        is not null
/

Regards
Michel
Re: Accented Vowels [message #316552 is a reply to message #316549] Fri, 25 April 2008 12:13 Go to previous messageGo to next message
ShridharV
Messages: 29
Registered: January 2008
Junior Member
Hi thanks for the reply. But Can you please explain me as to how it works?
Re: Accented Vowels [message #316554 is a reply to message #316544] Fri, 25 April 2008 12:27 Go to previous messageGo to next message
ShridharV
Messages: 29
Registered: January 2008
Junior Member
Also it is not listing all the values. Is there a way to get only the data that has english alphabets and nothing else.
Re: Accented Vowels [message #316555 is a reply to message #316554] Fri, 25 April 2008 12:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is there a way to get only the data that has english alphabets and nothing else.

This is not the same question.
Use TRANSLATE to remove all characters you want, the resulting values that are not null are those that contain characters you don't want.

Regards
Michel
Re: Accented Vowels [message #316557 is a reply to message #316555] Fri, 25 April 2008 12:56 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
For a more complete set:

SCOTT@orcl_11g> SELECT * FROM mytable
  2  /

MYCOL
------------------------------
a
e
u



trs
trees

8 rows selected.

SCOTT@orcl_11g> SELECT REPLACE (wm_concat (accents), ',')
  2  FROM   (SELECT CHR (192 + ROWNUM) AS accents
  3  	     FROM   DUAL
  4  	     CONNECT BY LEVEL <= 63)
  5  /

REPLACE(WM_CONCAT(ACCENTS),',')
--------------------------------------------------------------------------------


SCOTT@orcl_11g> select mycol
  2  from   mytable
  3  where  translate(mycol,
  4  		     ''||translate(mycol,'','a'),
  5  		     '')
  6  	    is not null
  7  /

MYCOL
------------------------------



trs

SCOTT@orcl_11g> 

Re: Accented Vowels [message #316558 is a reply to message #316555] Fri, 25 April 2008 12:58 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
and for the opposite, just change is not null to is null:

SCOTT@orcl_11g> select mycol
  2  from   mytable
  3  where  translate(mycol,
  4  		     ''||translate(mycol,'','a'),
  5  		     '')
  6  	    is null
  7  /

MYCOL
------------------------------
a
e
u
trees

SCOTT@orcl_11g> 

Re: Accented Vowels [message #316559 is a reply to message #316544] Fri, 25 April 2008 13:09 Go to previous messageGo to next message
ShridharV
Messages: 29
Registered: January 2008
Junior Member
Michel and Barbara,

Thanks a lot for your help. It is interesting.
Re: Accented Vowels [message #316562 is a reply to message #316554] Fri, 25 April 2008 13:44 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
ShridharV wrote on Fri, 25 April 2008 10:27
Also it is not listing all the values. Is there a way to get only the data that has english alphabets and nothing else.



select string_col from my_table where string_col = ASCIISTR(string_col);
Re: Accented Vowels [message #316564 is a reply to message #316562] Fri, 25 April 2008 14:07 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
anacedent wrote on Fri, 25 April 2008 11:44

select string_col from my_table where string_col = ASCIISTR(string_col);



Much simpler! I never stumbled across ASCIISTR before.

SCOTT@orcl_11g> SELECT mycol FROM mytable WHERE mycol = ASCIISTR (mycol)
2 /

MYCOL
------------------------------
a
e
u
trees

SCOTT@orcl_11g> SELECT mycol FROM mytable WHERE mycol <> ASCIISTR (mycol)
2 /

MYCOL
------------------------------



trs

SCOTT@orcl_11g>

Re: Accented Vowels [message #316568 is a reply to message #316562] Fri, 25 April 2008 14:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I understood "Is there a way to get only the data that has english alphabets" in a more restrictive way than ascii. Some characters in ascii are not "english" (for instance ~, I think).

Thanks to remind us ASCIISTR function.

Regards
Michel
Re: Accented Vowels [message #316571 is a reply to message #316544] Fri, 25 April 2008 15:05 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Much simpler! I never stumbled across ASCIISTR before.
There is a corresponding function UNISTR which provides reverse functionality.

Oracle universe is WAY TOO BIG for any 1 person to know all the answers.

ASCII - AMERICAN Standard Code for Information Interchange.
For almost 3 decades I have carried an ASCII card in my wallet.
On this side of the pond, printable ASCII characters are 32-126; inclusive (with 127 being the "DEL" character).

YMMV & HTH!

[Updated on: Fri, 25 April 2008 15:15] by Moderator

Report message to a moderator

Previous Topic: How to campare string in PL/SQL
Next Topic: Bfile Error - Please help
Goto Forum:
  


Current Time: Fri Dec 09 05:40:48 CST 2016

Total time taken to generate the page: 0.09843 seconds