Reputation: 2358
How replace last character of string with blank if last character is dots.
Example:
Select replace('Luis Nazario Ronaldo Da Lima.',' ','.') as Name from dual
union all
Select replace('Ronaldo de Assis Moreira',' ','.') as Name from dual
From first name need remove dots at the end of string, second name is ok.
I need this resault:
LuisNazarioRonaldoDaLima
RonaldodeAssisMoreira
Upvotes: 0
Views: 3115
Reputation: 522732
You could use REGEXP_REPLACE
:
SELECT REGEXP_REPLACE('Luis Nazario Ronaldo Da Lima.', ' |\.$', '') AS Name FROM dual
UNION ALL
SELECT REGEXP_REPLACE('Ronaldo de Assis Moreira', ' |\.$', '') FROM dual;
This outputs:
LuisNazarioRonaldoDaLima
RonaldodeAssisMoreira
The regex pattern used here is \.$
, and will target either spaces or the final character of the string if that final character be dot.
Upvotes: 1
Reputation:
You don't need regular expressions for this. Regular expressions are a big machinery, resulting in slow(er) execution. They are great when you can't solve the problem with standard string functions, but that's not the case here.
To remove one, two, or more trailing periods at the end of a string, just use RTRIM(..., '.')
Like this:
select original_name, rtrim(original_name, '.') as cleaned_up_name
from (
select 'Luis Nazario Da Lima.' as original_name from dual union all
select 'Ronaldo de Assis Moreira' from dual union all
select 'Pele...' from dual
)
;
ORIGINAL_NAME CLEANED_UP_NAME
--------------------------- -----------------------------
Luis Nazario Da Lima. Luis Nazario Da Lima
Ronaldo de Assis Moreira Ronaldo de Assis Moreira
Pele... Pele
Upvotes: 1
Reputation: 75
You must first place the character to be replaced. like this:
Select replace('Luis Nazario Ronaldo Da Lima.','.') as Name from dual
union all
Select replace('Ronaldo de Assis Moreira','.') as Name from dual
But if you want to remove all dots in all lines with a variable on colunm name, you should do some like this:
select decode(substr(your_colunm, 0, LENGTH(your_colunm) - 1),
'.',
substr(your_colunm, 0, LENGTH(your_colunm) - 1),
your_colunm) from your_table
Upvotes: 0