CODEWITHSUNDEEP
oracle-databasetrim
user_odoo
user_odoo

Reputation: 2358

Replace last character of string Oracle plsql

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

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

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;

Demo

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

user5683823
user5683823

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

Henique
Henique

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

Related Questions