CODEWITHSUNDEEP
sqlsql-serverreplace
Ar-Chi
Ar-Chi

Reputation: 9

Replacing same character multiple times with different replacement column in SQL

I have been scratching my head of how to do this with SQL server REPLACE or STUFF with SUBSTRING, CONCAT eg. without final resolution how to do it.

I have example a string:

'Product # has not been created for company # with language #'

What I would need to do is change each '#' with different result from different columns:

So end result should look like something like this:

'Product P0001 has not been created for company 1000 with language EN'

Upvotes: 0

Views: 335

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 82020

One option is to split the string on the # and then simply reconstruct it

Example

Declare @S varchar(max) = 'Product # has not been created for company # with language #'

Select concat(
              xDim.value('/x[1]','varchar(max)')
             ,'P0001'
             ,xDim.value('/x[2]','varchar(max)')
             ,'1000'
             ,xDim.value('/x[3]','varchar(max)')
             ,'EN'
             ,xDim.value('/x[4]','varchar(max)')  
             )
 From ( values ( cast('<x>'+ replace( (Select replace(@S,'#','§§Split§§') as [*] For XML Path('')),'§§Split§§', '</x><x>') + '</x>' as xml ))) as A(xDim)

Returns

NewValue
Product P0001 has not been created for company 1000 with language EN

Upvotes: 2

forpas
forpas

Reputation: 164214

With a recursive CTE:

create table tablename(data varchar(max), data1 varchar(100), data2 varchar(100), data3 varchar(100));
insert into tablename(data, data1, data2, data3) values
('Product # has not been created for company # with language #', 'P0001', '1000', 'EN'),
('First # second # third # end', '1st', '2nd', '3d'),
('# a # b # c', '1', '2', '3');

with 
  cte as (
    select 2 id, data1, data2, data3, 
      case 
        when charindex('#', data) = 0 then data
        else left(data, charindex('#', data) - 1) +
             data1 +
             substring(data, charindex('#', data) + 1, len(data))
      end data  
    from tablename
    union all
    select id + 1, data1, data2, data3, 
      case 
        when charindex('#', data) = 0 then data
        else left(data, charindex('#', data) - 1) + 
             choose(id, data1, data2, data3) +              
             substring(data, charindex('#', data) + 1, len(data)) 
      end
    from cte
    where charindex('#', data) > 0
  )
select data 
from cte 
where charindex('#', data) = 0

See the demo.
Results:

> | data                                                                 |
> | :------------------------------------------------------------------- |
> | 1 a 2 b 3 c                                                          |
> | First 1st second 2nd third 3d end                                    |
> | Product P0001 has not been created for company 1000 with language EN |

Upvotes: 1

Related Questions