Replace specific strings in two columns

Question

I would like to know most efficient and secure way to replace some numbers. In my table i have two columns: Nummer and Vater. In Nummer column i store articles numbers. The one with .1 at the end is the 'main' article and rest are his combinations (sometimes main article doesn't contain combinations). Numbers consist of 3-parts separated by 3 dots (always). Vater for all of them is always main article number as shown below:

Nummer        |   Vater
-------------------------------
003.10TT032.1   |  003.10TT032.1
003.10TT032.2L  |  003.10TT032.1
003.10TT032.UY  |  003.10TT032.1

Nummer column = varchar
Vater column = varchar

I want to have possibility to change first 2 parts n.n

For example i want to say and send via sql query that i want to replace to: 9.4R53 Therefore based on our example the final results should be as follows:

Nummer    |   Vater
----------------------
9.4R53.1   |  9.4R53.1
9.4R53.2L  |  9.4R53.1
9.4R53.UY  |  9.4R53.1

Hope this is clear.

Possible duplicate of [How to replace a string in a SQL Server Table Column](https://stackoverflow.com/questions/814548/how-to-replace-a-string-in-a-sql-server-table-column) — Peter B, Jan 08 '18 at 12:34

score 1 · Accepted Answer · answered Jan 08 '18 at 12:36

1

try This

DECLARE @MysTR VARCHAR(50) = '003.10TT032.1'
DECLARE @RepStr VARCHAR(50) = '9.4R53'

SELECT
    MyStr = @MysTR,
    FinalStr = @RepStr+REVERSE(SUBSTRING(
                        REVERSE(@MysTR),
                        1,
                        CHARINDEX('.',REVERSE(@MysTR))
                        ))

My Result

MyStr                                              FinalStr
003.10TT032.1                                      9.4R53.1

answered Jan 08 '18 at 12:36

Jayasurya Satheesh

7,826
3
22
39

Hi, What about replace isn't enough as suggested above? I am not sure ... – Arie Jan 08 '18 at 12:43
To use replace you have to find the position of the second Dot. So This one is more easier to reduce code complexity – Jayasurya Satheesh Jan 08 '18 at 12:49
Couldn't i just do like this? update table set Nummer = replace(Nummer, '003.10TT032', '9.4R53') where path LIKE '%003.10TT032.' – Arie Jan 08 '18 at 12:59
But this won't work if the First 2 parts are different, in that case, you need to write separate replace for each. But My code will work perfectly for each without the need to individual Replace – Jayasurya Satheesh Jan 08 '18 at 13:01
But all two first parts are the same within one product if you look at my example. – Arie Jan 08 '18 at 13:03
But, you don't have to change it across multiple products, instead, you can just use the same code. I'm trying to make it more generic – Jayasurya Satheesh Jan 08 '18 at 13:04
why dont you use where? And where is update for vater, could you update your query please – Arie Jan 08 '18 at 13:38
You don't need the where since it a dynamic query. And this is a demo so you can do it in your table by yourself – Jayasurya Satheesh Jan 08 '18 at 15:23

score 1 · Answer 2 · answered Jan 08 '18 at 12:44

Please try this

DECLARE @Replace VARCHAR(50) = '9.4R53'
SELECT  CONCAT(@Replace,SUBSTRING(Nummer,CHARINDEX('.',Nummer,5),1000)) Nummer
       ,CONCAT(@Replace,SUBSTRING(Vater,CHARINDEX('.',Vater,5),1000)) Vater
FROM REplaces

OUTPUT

Nummer                         Vater
------------------------------ ---------------
9.4R53.1                       9.4R53.1
9.4R53.2L                      9.4R53.1
9.4R53.UY                      9.4R53.1

(3 rows affected)

Hi, What about replace isn't enough as suggested above? I am not sure — Arie, Jan 08 '18 at 12:44

score 0 · Answer 3 · answered Jan 08 '18 at 12:58

As you mentioned your format is fixed, you can use PARSENAME to achieve your expectation:

Sample data:

DECLARE @TestTable TABLE (Nummer VARCHAR (100), Vater VARCHAR (100))

INSERT INTO @TestTable (Nummer, Vater)
SELECT '003.10TT032.1',  '003.10TT032.1' UNION
SELECT '003.10TT032.2L', '003.10TT032.1' UNION
SELECT '003.10TT032.UY', '003.10TT032.1';

DECLARE @TextToBeReplaced AS VARCHAR (100) = '9.4R53.';

SELECT  @TextToBeReplaced + PARSENAME(Nummer, 1) AS Nummer, 
        @TextToBeReplaced + PARSENAME(Vater, 1) AS Vater
FROM @TestTable

Result:

Nummer      | Vater
-----------------------
9.4R53.1    | 9.4R53.1
9.4R53.2L   | 9.4R53.1
9.4R53.UY   | 9.4R53.1

Replace specific strings in two columns

3 Answers3