OraFAQ Forum: SQL & PL/SQL » Finding characters other than keyboard character

Home » SQL & PL/SQL » SQL & PL/SQL » Finding characters other than keyboard character (Oracle 10g)

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

Finding characters other than keyboard character [message #665776]

Wed, 20 September 2017 07:46

sss111ind
Messages: 636
Registered: April 2012
Location: India

Senior Member

Hi All,

We want to find special character if anything present other than keyboard characters.Such as in the below string one similar character is present.

# 2166 SECTOR 44C CHANDIGARH C¥O # 117 SENIOR CITIZEN SOCIETY SECTOR 48C MOHALI MOHALI 160055

Regards,
Nathan

Report message to a moderator

Re: Finding characters other than keyboard character [message #665777 is a reply to message #665776]

Wed, 20 September 2017 07:53

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

sss111ind wrote on Wed, 20 September 2017 05:46

Hi All,

We want to find special character if anything present other than keyboard characters.Such as in the below string one similar character is present.

# 2166 SECTOR 44C CHANDIGARH C¥O # 117 SENIOR CITIZEN SOCIETY SECTOR 48C MOHALI MOHALI 160055

Regards,
Nathan

so remove all keyboard characters & what is left, if any, are "special" characters.

Report message to a moderator

Re: Finding characters other than keyboard character [message #665779 is a reply to message #665777]

Wed, 20 September 2017 08:04

quirks
Messages: 85
Registered: October 2014

Member

As long as you can't access the keyboard driver to find out which keyboard is present you just don't know what characters are available. But your question sounds a little bit like the one asked here:
http://www.orafaq.com/forum/t/203836/
You might find a solution for your question in the answers given.

If it does not help you to solve your problem, feel free to ask a more specific question. Wink

[Updated on: Wed, 20 September 2017 08:06]

Report message to a moderator

Re: Finding characters other than keyboard character [message #665780 is a reply to message #665779]

Wed, 20 September 2017 09:19

sss111ind
Messages: 636
Registered: April 2012
Location: India

Senior Member

Thank You All,

I am missing Michel sir,he always would be the first person to respond any messages.We found one solution from ASKTOM and in the following way we have handled the scenario.

SELECT
    *
FROM
    dual
WHERE
    replace(
        translate(
            '~' || '# 2166 SECTOR 44C CHANDIGARH C¥O # 117 SENIOR CITIZEN SOCIETY SECTOR 48C MOHALI MOHALI 160055',
            'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 ,./%+:&#_";[]<>@=|{}()?!$~',
            'x'
        ),
        'x',
        ''
    ) IS NOT NULL;

Regards,
Nathan

[Updated on: Wed, 20 September 2017 09:20]

Report message to a moderator

Re: Finding characters other than keyboard character [message #665867 is a reply to message #665780]

Wed, 27 September 2017 06:41

sss111ind
Messages: 636
Registered: April 2012
Location: India

Senior Member

Hi All,

We have removed special characters by using space instead of null in this way.However if we try with null the result becomes null.

SELECT 
TRANSLATE('Off¿¿Jogeshwari Vikroli¿¿Road159 ¥A,1St.Floor,¥Govind ¥Prasad,¥Bomikha ¿¿ l',
REPLACE(
translate(  'Off¿¿Jogeshwari Viakroli¿¿Road159 ¥A,1St.Floor,Govind ¥Prasad,¥Bomikha ¿¿ l',
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 ,./\%+:;&#-_"[]<>@=|{}()?!$', 'x' )
,'x' ,'')
,' ' )--space instead of null
RESULTT
FROM dual;

Regards,
Nathan

[Updated on: Wed, 27 September 2017 06:44]

Report message to a moderator

Re: Finding characters other than keyboard character [message #665868 is a reply to message #665867]

Wed, 27 September 2017 10:41

_jum
Messages: 577
Registered: February 2008

Senior Member

Why didn't you follow quirks link (replace string adapted)?

--replace with ''
SELECT regexp_replace(
'Off¿¿Jogeshwari Vikroli¿¿Road159 ¥A,1St.Floor,¥Govind ¥Prasad,¥Bomikha ¿¿ l',
'[^] abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0-9.,;:''+()?*[{}\`´~!"#%&]','') rstr
  FROM dual;   

RSTR
-----
OffJogeshwari VikroliRoad159 A,1St.Floor,Govind Prasad,Bomikha  l

--replace with '*'
SELECT regexp_replace(
'Off¿¿Jogeshwari Vikroli¿¿Road159 ¥A,1St.Floor,¥Govind ¥Prasad,¥Bomikha ¿¿ l',
'[^] abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0-9.,;:''+()?*[{}\`´~!"#%&]','*') rstr
  FROM dual;   

RSTR
-----
Off**Jogeshwari Vikroli**Road159 *A,1St.Floor,*Govind *Prasad,*Bomikha ** l

[Updated on: Wed, 27 September 2017 10:43]

Report message to a moderator

Re: Finding characters other than keyboard character [message #666023 is a reply to message #665868]

Mon, 09 October 2017 05:38

sss111ind
Messages: 636
Registered: April 2012
Location: India

Senior Member

Thank you _jum we have used quirks query because for valid cases(no special characters string) the translate query is not working.

Regards,
Nathan

Report message to a moderator

Previous Topic:	SQL Query Help
Next Topic:	Left Outer Join with SubQuery

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Nov 03 19:01:19 CST 2025