Thanks Chris
sona sh, February 25, 2016 - 10:51 am UTC
Thanks a ton Chris,It is working fine now.If i will get any further additional add ons on the requirement .i will contact you.
Regards
sonam
February 25, 2016 - 11:16 am UTC
Glad we could help
sona sh, February 25, 2016 - 10:58 am UTC
Thanks Chris
sona sh, February 25, 2016 - 10:58 am UTC
Thanks a lot Chris,It is working fine now.
Thanks Chris
sona sh, February 25, 2016 - 11:01 am UTC
Thanks a lot Chris,It is working fine now.
Thanks Chris
sona sh, February 25, 2016 - 11:03 am UTC
Thanks a lot Chris,It is working fine now.
Thanks Chris
sona sh, February 25, 2016 - 11:04 am UTC
Thanks a lot Chris,It is working fine now.
Other Alternatives - Query to remove multiple SPACE
Rajeshwaran Jeyabal, February 25, 2016 - 12:51 pm UTC
Query to remove multiple SPACE using Regexp and non-Regexp versions.
drop table t purge;
create table t(x varchar2(80));
insert into t values('Archivos de biologia y medicina exp');
insert into t values('Archivos de Psiquiatria');
insert into t values('Archivos de pediatria del Uruguay');
insert into t values('Archivos dominicanos de pediatria');
insert into t values('Archivos espanoles de urologia');
insert into t values('Archivos latinoame ricanos de nutricion');
commit;
rajesh@ORA11G> select x,
2 replace(replace(replace(x,' ','@ '),' @'),'@') as replace1,
3 regexp_replace(x,'( ){2,}','\1') as replace2
4 from t;
X REPLACE1 REPLACE2
------------------------------------------------------- ------------------------------------------------------- ---------------------------------
Archivos de biologia y medicina exp Archivos de biologia y medicina exp Archivos de biologia y medicina exp
Archivos de Psiquiatria Archivos de Psiquiatria Archivos de Psiquiatria
Archivos de pediatria del Uruguay Archivos de pediatria del Uruguay Archivos de pediatria del Uruguay
Archivos dominicanos de pediatria Archivos dominicanos de pediatria Archivos dominicanos de pediatria
Archivos espanoles de urologia Archivos espanoles de urologia Archivos espanoles de urologia
Archivos latinoame ricanos de nutricion Archivos latinoame ricanos de nutricion Archivos latinoame ricanos de nutricion
6 rows selected.
rajesh@ORA11G>
February 25, 2016 - 5:53 pm UTC
Assuming that @ isn't a character you need to keep of course! ;)
Thanks Chris
sona sh, February 25, 2016 - 2:18 pm UTC
Thanks a lot.It is working Fine now.
Thanks Chris
sona sh, March 08, 2016 - 11:36 am UTC
Hi Chris,
I am working on a project which is based on sql and sql script actually.Can you please guide me on the same.
Can you please tell me from where i can read and try writing the query effeciently.
Thanks & Regards
Sonam
March 08, 2016 - 5:26 pm UTC
I'm not sure what you're looking for. If you have a new question then please post a new one rather than asking more here.
Likitha, October 02, 2017 - 8:07 pm UTC
Very helpful...Thanks a lot....!!!
control characters from text and special characters and exceptions
Imran, February 14, 2019 - 12:14 pm UTC
Hello Chris,
I am still having problems in finding the correct query in Oracle Output that can show me in a particular column ex. 3 columns in a table like first_name, last_name and full_name:
first_name last_name full_name
-------------------------------
1.John Smith John Smith
2.João Sm!?th João Sm!?th
3.João Smith João Smith
4.John Smíth John Smíth
5.J?hn Sm|ith J?hn Sm|th
-----------------------------------------
Output: Should be:
1.João Sm!?th João Sm!?th
2.J?hn Sm|ith J?hn Sm|th
Permitted Characters (Below):
-----------------------------
ÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜ'
ACEIOUAEIOUAEIOUAOEU, A-Z, a-z, 0-9
Should
NOT be included such characters(Below):
----------------------------------------------
Restricted Like
! Like
" Like
# Like
& Like
( Like
) Like
, Like
: Like
; Like
? Like
[ Like
] Like
` Like
| Like
I saw your post here:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9526745900346594796 but couldn´t still find out the solution.
February 21, 2019 - 5:31 pm UTC
I'm not sure exactly what you're asking here. Please provide a test case in the form of:
create table
insert into
expected results
Anil kumar, July 30, 2019 - 11:22 am UTC
How to keep [] in result, as [] are not a special characters.
as you sql REGEXP_REPLACE(REGEXP_REPLACE(NM, '[^a-zA-Z0-9`~!@#$%^&*()_+-=|\}{:";'',./<>? ]', ''), ' {2,}', ' ') removed [] characters from results.
July 30, 2019 - 1:04 pm UTC
You're replacing any character which is NOT in the list. Square brackets aren't in the list!
If you want to keep these, you need to add them to the regex.
Geeting special character in place of original character
Sitaram, August 28, 2019 - 2:13 pm UTC
I have character like '-' and '?' in my source .but when i am loading in to target (oracle DB),its coming as '[]' and '!'.
how to handle it in sql query .so the same value will be updated in Target.
August 29, 2019 - 10:17 am UTC
How exactly are you doing that?
Double translate option
Mark Wooldridge, August 29, 2019 - 5:55 pm UTC
Years ago I found a post on this site where a double translate was used to remove bad characters from a string. I have used this function many times over the years.
The assumption is you know the list of characters that are good/acceptable and have 1 character you know is bad (inner translate removes all of the good characters leaving only the bad).
function remove_bad_chars(p_str in varchar2,
p_good_chars in varchar2,
p_bad_char in char)
return varchar2 is
begin
return translate(p_str, substr(p_good_chars, 1, 1)||translate(p_str, p_bad_char||p_good_chars, p_bad_char), substr(p_good_chars, 1, 1));
end remove_bad_chars;
September 02, 2019 - 10:04 am UTC
Thanks for digging this out.
Remove extra spaces using recursive with
Mark Wooldridge, August 29, 2019 - 6:21 pm UTC
How about a recursive with?
var s varchar2(100)
exec :s := 'this is a test to remove extra spaces';
exec :s := 'ABC PRIVATE LTD';
with q(str) as (
select :s
from dual
union all
select replace(str, ' ', ' ')
from q
where instr(str, ' ') > 0)
select trim(str) --** trim to remove any leading/trailing spaces
from q
where instr(str, ' ') = 0
/
September 02, 2019 - 10:09 am UTC
Cool, but I prefer the "double translate" method you posted before. Depending on what you're doing and the input, you could end up running lots of recursive branches.
nice and clean solution
Ying Wang, April 13, 2021 - 2:00 pm UTC
This is way much better than translate. Thank you so much Chris!
April 13, 2021 - 4:26 pm UTC
You're welcome
special characters
Tanis, September 08, 2021 - 4:00 pm UTC
select regexp_replace('TaqMan*^?!% Universal PCR Master Mix','[^'||chr(1)||'-'||chr(127)||']', '|') from dual;
September 09, 2021 - 5:40 am UTC
Interesting string choice :-)
Regrading About Removing of Special Characters,Spaces,and Numeric Value in a string
sai, January 31, 2022 - 7:01 am UTC
Hi,
How Removing of Special Characters, Spaces, and Numeric Value in a string
EX:123$+james*+-$%@gmail.com
Output:James
January 31, 2022 - 11:15 am UTC
You could replace everything that's NOT a letter, e.g.:
select regexp_replace (
'123$+james*+-$%@gmail.com',
'[^[:alpha:]]'
) str
from dual
STR
-------------
jamesgmailcom
Replacing special characters with their equivalent
SK, April 20, 2022 - 5:17 pm UTC
Hi ..I have a string JOSÉ and need it to be replaced as JOSE. similarly for other such characters like Á, Î . Is there a way to do this in oracle 12 plsql?
April 21, 2022 - 12:22 pm UTC
You can use REPLACE as with any other substitution.
The CONVERT function maps from one character set to another, so may help in some cases. But it can give unexpected outcomes if the conversion is not possible:
SELECT CONVERT('Ä Ê Í Õ Ø A B C D E ', 'US7ASCII' )
FROM DUAL;
A E I ? ? A B C D E
What would be the oracle procedure to validate string with special chars
Arun, August 25, 2022 - 12:18 pm UTC
Hi Chris, I have gone through your responses, which were amazing,
Hope you help me out here,
So,In columns like First name, Middle name and Last name, if we have already permitted special characters while storing data without validation. so, now if we want to validate that data to restrict special chars, to be stored in another table/upcoming inputs. for the string which have special chars the alerts should be like, "this string have special characters". and string without special chars are allowed to get stored in the table.
NOTE: I'm not anticipating that special char string should eliminate the special chars, only I need validate to deliver the "msg". I need oracle procedure/sql query. Kindly help.
August 25, 2022 - 1:24 pm UTC
You could do a variation of one of the above solutions - remove everything which is a letter. If the resulting string has characters => they're special => raise an error