OraFAQ Forum: SQL & PL/SQL » find Characters which are not present in keyboard

Home » SQL & PL/SQL » SQL & PL/SQL » find Characters which are not present in keyboard (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

find Characters which are not present in keyboard [message #649714]

Mon, 04 April 2016 10:19

sss111ind
Messages: 634
Registered: April 2012
Location: India

Senior Member

Hi All,

How to find characters which are not present in keyboard as given in the below query. Any help for this is greatly appreciated.

SELECT STATUS,
  STR
FROM
  ( SELECT 'special character in Query123' STR ,'valid' STATUS FROM DUAL
  UNION ALL
  SELECT 'special character in Query123¥' STR ,'invalid' STATUS FROM DUAL
  );

Regards,
Nathan

[Updated on: Mon, 04 April 2016 10:20]

Report message to a moderator

Re: find Characters which are not present in keyboard [message #649715 is a reply to message #649714]

Mon, 04 April 2016 11:19

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Oracle DB has no way of knowing anything about the keyboard.

Report message to a moderator

Re: find Characters which are not present in keyboard [message #649723 is a reply to message #649714]

Mon, 04 April 2016 15:51

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

Please see the example below. You may wish to modify it, depending on what is on your keyboard and you may or may not want to include things like combinations of the Ctrl key and other characters.

SCOTT@orcl> WITH
  2    test_data AS
  3  	 (SELECT 'special character in Query123' STR FROM DUAL
  4  	  UNION ALL
  5  	  SELECT 'special character in Query123¥' STR FROM DUAL)
  6  SELECT NVL2 (REPLACE (TRANSLATE (str,
  7  	      -- LIST ALL KEYBOARD CHARACTERS IN 2 LINES BELOW (INCLUDING SPACE, TAB, AND LINEFEED):
  8  	      '      ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890
  9  	       `~!@#$%^&*()-_=+{[}]|\:;"''<,>.?/',
 10  	      'X'), 'X', ''),'invalid', 'valid') status,
 11  	    REPLACE (TRANSLATE (str,
 12  	      -- LIST ALL KEYBOARD CHARACTERS IN 2 LINES BELOW (INCLUDING SPACE, TAB, AND LINEFEED):
 13  	      '      ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890
 14  	       `~!@#$%^&*()-_=+{[}]|\:;"''<,>.?/',
 15  	      'X'), 'X', '') invalid_characters,
 16  	    str
 17  FROM   test_data
 18  /

STATUS  INVALID_CHARACTERS STR
------- ------------------ -------------------------------
valid                      special character in Query123
invalid ¥                  special character in Query123¥

2 rows selected.

You might also be able to make use of regular expressions. The following is just an example that works with the test data provided, but may need modification, depending on what you want.

SCOTT@orcl> WITH
  2    test_data AS
  3  	 (SELECT 'special character in Query123' STR FROM DUAL
  4  	  UNION ALL
  5  	  SELECT 'special character in Query123¥' STR FROM DUAL)
  6  SELECT NVL2 (REGEXP_REPLACE (str,
  7  	      '[[:alnum:]]',
  8  	      ''),'invalid', 'valid') status,
  9  	    REGEXP_REPLACE (str,
 10  	      '[[:alnum:]]',
 11  	      '') invalid_characters,
 12  	    str
 13  FROM   test_data
 14  /

STATUS  INVALID_CHARACTERS             STR
------- ------------------------------ -------------------------------
invalid                                special character in Query123
invalid    ¥                           special character in Query123¥

2 rows selected.

[Updated on: Mon, 04 April 2016 16:15]

Report message to a moderator

Previous Topic:	Random distribution of sum
Next Topic:	GROUP BY with 3 tables

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 19:47:46 CDT 2024