CODEWITHSUNDEEP
sqlsql-serverreplacesql-update
Kairo
Kairo

Reputation: 11

Updating a Portion of a String in SQL Server

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:

Supplier
test-abcd123-value

I want to update it to:

Supplier
test-testing321-value

How do you update query that?

Upvotes: 1

Views: 100

Answers (3)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22177

Please try the following solution.

It will work starting from SQL Server 2017 onwards due to the use of the CONCAT_WS() function. If needed, it is very easy to switch to the older CONCAT() function.

It is using tokenization via XML/XQuery, and checking for exact count of tokens in the column.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, supplier VARCHAR(50));
INSERT INTO @tbl (supplier) VALUES 
('FirstToken-abcd123-ThirdToken'), 
('FirstToken-yaddyyaddy-ThirdToken'), 
('FirstToken-bla bla bla-ThirdToken'), 
('FirstToken-DoNothing');
-- DDL and sample data population, end

-- before
SELECT * FROM @tbl;

DECLARE @separator CHAR(1) = '-'
    , @replaceWith varchar(20) = 'NewMiddleToken';

;WITH rs AS
(
    SELECT *
        , new_supplier = CONCAT_WS(@separator
            , c.value('(/root/r[1]/text())[1]', 'VARCHAR(30)')
            , @replaceWith
            , c.value('(/root/r[3]/text())[1]', 'VARCHAR(30)'))
    FROM @tbl AS t
        CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' + 
              REPLACE(supplier, @separator, ']]></r><r><![CDATA[') + 
              ']]></r></root>' AS XML)) AS t1(c)
        CROSS APPLY (SELECT c.value('count(/root/r)', 'INT')) AS t3(cnt)
    WHERE cnt = 3
)
UPDATE t
SET t.supplier = rs.new_supplier
FROM @tbl AS t INNER JOIN 
    rs ON t.ID = rs.ID;

--after
SELECT * FROM @tbl;

Output

ID supplier
1 FirstToken-NewMiddleToken-ThirdToken
2 FirstToken-NewMiddleToken-ThirdToken
3 FirstToken-NewMiddleToken-ThirdToken
4 FirstToken-DoNothing

Upvotes: 0

Zhorov
Zhorov

Reputation: 29943

You may try to get the positions of the hyphens using CHARINDEX() and update the text using STUFF() and 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

Upvotes: 1

GuidoG
GuidoG

Reputation: 12014

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
       -- it would be so nice if you could write it like this, but you have te repeat the statement above here or make yet another subquery
       --replace(t.supplier, to_replace, '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

supplier startpos length to_replace end_result
test-abcd123-value 6 7 abcd123 test-testing321-value
test-yaddyyaddy-value2 6 10 yaddyyaddy test-testing321-value2
test-bla bla bla-value 6 11 bla bla bla test-testing321-value

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

supplier startpos length to_replace end_result
test-abcd123-value 6 7 abcd123 test-testing321-value
test-yaddyyaddy-value2 6 10 yaddyyaddy test-testing321-value2
test-bla bla bla-value 6 11 bla bla bla test-testing321-value
test-nothing 6 -6 test-nothing

Upvotes: 0

Related Questions