Home » SQL & PL/SQL » SQL & PL/SQL » Finding characters other than keyboard character (Oracle 10g)
Finding characters other than keyboard character [message #665776] Wed, 20 September 2017 07:46 Go to next message
sss111ind
Messages: 634
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
Re: Finding characters other than keyboard character [message #665777 is a reply to message #665776] Wed, 20 September 2017 07:53 Go to previous messageGo to next message
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.
Re: Finding characters other than keyboard character [message #665779 is a reply to message #665777] Wed, 20 September 2017 08:04 Go to previous messageGo to next message
quirks
Messages: 82
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 Go to previous messageGo to next message
sss111ind
Messages: 634
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 Go to previous messageGo to next message
sss111ind
Messages: 634
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 Go to previous messageGo to next message
_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 Go to previous message
sss111ind
Messages: 634
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
Previous Topic: SQL Query Help
Next Topic: Left Outer Join with SubQuery
Goto Forum:
  


Current Time: Fri Mar 29 09:15:09 CDT 2024