Home » SQL & PL/SQL » SQL & PL/SQL » Finding characters other than keyboard character (Oracle 10g)
|
|
|
Re: Finding characters other than keyboard character [message #665780 is a reply to message #665779] |
Wed, 20 September 2017 09:19 |
|
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 |
|
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 |
_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
|
|
|
|
Goto Forum:
Current Time: Thu Apr 18 22:03:28 CDT 2024
|