CODEWITHSUNDEEP
sqlsql-server
Peter_07
Peter_07

Reputation: 117

SQL replace a string starting with a specific character

I have a column with text/sentences that may include strings starting with '@'. I need to remove all strings starting with '@' from the texts. For example:

enter image description here

Is there any function that can do the trick? I have tried the following but this removes only the '@' from the string:

SELECT  Column1, 
        LTRIM(SUBSTRING(Column1, CHARINDEX('@',Column1)+1, LEN(Column1))) AS [Output column]
FROM mytable
WHERE Column1 like '%@%';

Upvotes: 0

Views: 2174

Answers (2)

lptr
lptr

Reputation: 6788

...for varchar/char only (not for Nvar/Nchar)..it's bad :(

declare @t table(id int identity, col1 varchar(100));
insert into @t(col1)
values
('aaaa @bbb cccc ddd @ffff'),
('@aaaa @bbb cccc ddd @ffff @@@@       '),
('@aaaa @bbb cc@@@@@cc  <-- nonleading@ ddd @ffff @@@@       '), 
('123456   '), ('@@@@@@')
;

select *, col1+'{...', 
    cast(
    convert(varbinary(100), 
    replace(
    cast('<!--' + replace(convert(varchar(max), cast(t.col1 as varbinary(100)) , 2), convert(varchar(100), cast(' ' as varbinary(4)), 2), '--><!--') + '-->' as xml).query('data(comment()[substring(., 1, 2) != "40"])').value('.', 'varchar(100)'), 
    ' ',
    convert(varchar(10), cast(' ' as varbinary(2)), 2)), 2)
    as varchar(100)) + '{...'
from @t as t;

Upvotes: 2

forpas
forpas

Reputation: 164064

One way to do it is with a recursive CTE:

with cte as (
  select Column1 col, Column1 + ' ' Column1, 1 level from mytable
  union all
  select col,
         left(Column1, charindex('@', Column1) - 1) +
         right(Column1, len(Column1) - charindex(' ', Column1, charindex('@', Column1)) + 1),
         level + 1
  from cte
  where charindex('@', Column1) > 0
)
select distinct rtrim(first_value(Column1) over (partition by col order by level desc)) Column1
from cte

See the demo.
Results:

> | Column1                               |
> | :------------------------------------ |
> | another text text continues here word |
> | some text here text continue here     |

Upvotes: 3

Related Questions