I want to update the part of the string between the hyphens in SQL Server. So I have a column named Supplier with a value like this:

I want to update it to:

How do you update query that?

CodePudding user response：

You may try to get the positions of the hyphens and update the text using the STUFF() function and the appropriate calculations:

; WITH cte AS (
   SELECT
      Supplier,
      CHARINDEX('-', Supplier) AS p1, 
      CHARINDEX('-', Supplier, CHARINDEX('-', Supplier)   1) AS p2
   FROM Data    
)  
UPDATE cte
SET Supplier = STUFF(Supplier, p1   1, p2 - p1 - 1, 'newvalue')  
WHERE p1 > 0 AND p2 > 0

CodePudding user response：

You could find the positions of the hyphens,
with that get the part you want to replace,
and then use the replace function

The query below shows all the necessary stages that you need

declare @test table (supplier varchar(50))

insert into @test (supplier) values ('test-abcd123-value'), ('test-yaddyyaddy-value2'), ('test-bla bla bla-value'), ('test-nothing')

select t.supplier,
       t.startpos,
       t.length,
       substring(t.supplier, t.startpos, t.length) as to_replace,
       replace(t.supplier, substring(t.supplier, t.startpos, t.length), 'testing321') end_result
from   ( select t.supplier,
                charindex('-', supplier)   1 startpos,
                (charindex('-', supplier, charindex('-', supplier)   1)) - (charindex('-', supplier)) - 1 length  
         from   @test t
       ) t  
where  t.startpos > 0
and    t.length > 0

this results in

in case you want to show all rows, also the ones you can not replace, you can use this

declare @test table (supplier varchar(50))

insert into @test (supplier) values ('test-abcd123-value'), ('test-yaddyyaddy-value2'), ('test-bla bla bla-value'), ('test-nothing')

select t.supplier,
       t.startpos,
       t.length,
       
       case when t.startpos > 0 and t.length > 0 then
                 substring(t.supplier, t.startpos, t.length)
            else null
       end as to_replace,
       
       case when t.startpos > 0 and t.length > 0 then
                 replace(t.supplier, substring(t.supplier, t.startpos, t.length), 'testing321')
            else t.supplier
       end as end_result
from   ( select t.supplier,
                charindex('-', supplier)   1 startpos,
                (charindex('-', supplier, charindex('-', supplier)   1)) - (charindex('-', supplier)) - 1 length  
         from   @test t
       ) t  

and the result is