CODEWITHSUNDEEP
sqloracle-database
arsha
arsha

Reputation: 67

remove the space between words when the strings ends with a number in sql

The length of my character is 30. I got to remove the last space from the string if it ends with a number .

Eg. COADC Cathy & Ralph Ward Jr 73 should be COADC Cathy & Ralph WardJr73
and not COADCCathy&RalphWardJr73

Tried regexp_like to get the strings that end with numbers and then tried trim() and replace() spaces but did not work out. is there any other function?

Upvotes: 0

Views: 73

Answers (2)

GSazheniuk
GSazheniuk

Reputation: 1384

declare @s varchar(100)
set @s = 'COADC Cathy & Ralph Ward Jr 73'

set @s = case 
            when  isnumeric(right(@s, 1)) = 1 and len(@s) = 30 then reverse(STUFF(reverse(@s), PATINDEX('% %', reverse(@s)), 1, '')) 
            else @s 
        end

set @s = case 
            when  isnumeric(right(@s, 1)) = 1 and len(@s) = 29 then reverse(STUFF(reverse(@s), PATINDEX('% %', reverse(@s)), 1, '')) 
            else @s 
        end

select @s, len(@s)

When you run this query for the first time, it will remove one last space from all strings that are 30 characters long and end with a numbers.

After that simply change len(@s) = 30 to len(@s) = 29 and run it again.

It is not perfect solution, but I don't see any reason for it not to work.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270061

remove the last space from the string if it ends with a number .

You can use regexp_replace():

select regexp_replace(str, ' ([0-9]+$)', '\1')
from (select 'COADC Cathy & Ralph Ward Jr 73' as str from dual) x;

[Here][1] is a db<>fiddle.

Note that your example removes the last two spaces. This example is following your description, not your example. It also interprets "ends with a number" as "only digits follow the last space".

[1]: https://dbfiddle.uk/?> remove the last space from the string if it ends with a number . rdbms=oracle_11.2&fiddle=995e20520454ee8ffb8030ab156f7431

Upvotes: 1

Related Questions