Home » SQL & PL/SQL » SQL & PL/SQL » Query to find special alphabets (Oracle 11g Release 2, Linux)
Query to find special alphabets [message #655454] Wed, 31 August 2016 05:43 Go to next message
anil_mk
Messages: 146
Registered: August 2006
Location: Bangalore, India
Senior Member

Hi,


How to identify special alphabets like É using SQL query

with spec_alpha_chars as (
select 'DÉLL' name from dual union
select 'QUÉST' name from dual  union
select 'IBM' name from dual
 )
select * from spec_alpha_chars


Thanks
Anil MK
Re: Query to find special alphabets [message #655462 is a reply to message #655454] Wed, 31 August 2016 06:16 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
remove all non-special characters & what is left are the characters you desire.
Re: Query to find special alphabets [message #655537 is a reply to message #655462] Thu, 01 September 2016 05:21 Go to previous messageGo to next message
ALEXWE
Messages: 11
Registered: August 2016
Location: Germany
Junior Member
Regexp:

--all valid characters
select regexp_replace('DÉLL','[DELL]','') from dual;
--or negate all invalid characters
select regexp_replace('DÉLL','[^É]','') from dual;
Example:
--valid
SELECT CHR(1+level) FROM dual CONNECT BY level <= 121;
--invalid
SELECT CHR(1+level) FROM dual WHERE level > 121 CONNECT BY level <= 254 ;

--replace match pattern  (convert Fails)

WITH spec_alpha_chars AS
  ( SELECT 'DÉLL' name FROM dual
  UNION
  SELECT 'QUÉST' name FROM dual
  UNION
  SELECT 'IBM' name FROM dual
  )
SELECT regexp_replace(name,'[^{|}~€‚ƒ„...†‡ˆ‰Š‹ŒŽ''""•--˜™š›œžŸ ¡¢£¤¥¦§¨©ª«¬­®¯°±²³´µ¶·¸¹º»¼½¾¿ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõö÷øùúûüýþÿ]',''),
  name
FROM spec_alpha_chars
WHERE regexp_like(name,'[{|}~€‚ƒ„...†‡ˆ‰Š‹ŒŽ''""•--˜™š›œžŸ ¡¢£¤¥¦§¨©ª«¬­®¯°±²³´µ¶·¸¹º»¼½¾¿ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõö÷øùúûüýþÿ]');

Re: Query to find special alphabets [message #655538 is a reply to message #655537] Thu, 01 September 2016 05:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

TRANSLATE will be FAR faster.
In addition, you miss some "invalid" characters.
The best way is the one mentioned by BlackSwan: remove all the valid characters what is left is invalid.

Re: Query to find special alphabets [message #655539 is a reply to message #655538] Thu, 01 September 2016 06:22 Go to previous messageGo to next message
Littlefoot
Messages: 21809
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Also, there's no need for regular expression here:
regexp_replace('DÉLL','[DELL]','')
as REPLACE does that well.

However, isn't it nice to see a new forum member who tries to help someone and - doing that - posts a nicely formatted message?
Re: Query to find special alphabets [message #655540 is a reply to message #655539] Thu, 01 September 2016 06:33 Go to previous message
Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It is! Smile

Previous Topic: QUERY HELP
Next Topic: Mail Notification
Goto Forum:
  


Current Time: Sat May 18 18:15:06 CDT 2024