Home » SQL & PL/SQL » SQL & PL/SQL » How to identify special character (Oracle 11g)
How to identify special character [message #609861] Thu, 13 March 2014 05:52 Go to next message
na.dharma@gmail.com
Messages: 82
Registered: May 2008
Location: bangalore
Member

Hi

I have a table sce_att as SCENARIO_SHORT_NAME, SCENARIO_LONG_NAME columns.

I want to identify which row of scenario_short_name and scenario_long_name as any special character appearing

for example:

CREATE TABLE SCE_ATT
(
  SCENARIO_SHORT_NAME  VARCHAR2(100 BYTE),
  SCENARIO_LONG_NAME   VARCHAR2(100 BYTE)
);

Insert into SCE_ATT
   (SCENARIO_SHORT_NAME, SCENARIO_LONG_NAME)
 Values
   ('EquityUp$%#@%', 'EquityUp$%#@%');
Insert into SCE_ATT
   (SCENARIO_SHORT_NAME, SCENARIO_LONG_NAME)
 Values
   ('GWWR_EMEA_DEV_FIN_SOV2', 'GWWR_EMEA_DEV_FIN_SOV1');
Insert into SCE_ATT
   (SCENARIO_SHORT_NAME, SCENARIO_LONG_NAME)
 Values
   ('EquityDown', 'FINMAIRDownExtreme');

COMMIT;
Re: How to identify special character [message #609863 is a reply to message #609861] Thu, 13 March 2014 06:04 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> select * from sce_att
  2  where length(translate(SCENARIO_SHORT_NAME,'A$%#@%','A'))!=length(SCENARIO_SHORT_NAME)
  3     or length(translate(SCENARIO_LONG_NAME,'A$%#@%','A'))!=length(SCENARIO_LONG_NAME)
  4  /
SCENARIO_SHORT_NAME
---------------------------------------------------------------------------------------------
SCENARIO_LONG_NAME
---------------------------------------------------------------------------------------------
EquityUp$%#@%
EquityUp$%#@%

1 row selected.

Previous Topic: Oracle 11g R2
Next Topic: ORA-01460: unimplemented or unreasonable conversion requested
Goto Forum:
  


Current Time: Thu Mar 28 06:02:36 CDT 2024