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 — Peter B
– Peter B, Commented Jan 8, 2018 at 12:34

Jayasurya Satheesh · Accepted Answer · 2018-01-08 12:36:36Z

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 8, 2018 at 12:36

Jayasurya Satheesh

8,0733 gold badges25 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Arie Over a year ago

Hi, What about replace isn't enough as suggested above? I am not sure ...

Jayasurya Satheesh Over a year ago

To use replace you have to find the position of the second Dot. So This one is more easier to reduce code complexity

Arie Over a year ago

Couldn't i just do like this? update table set Nummer = replace(Nummer, '003.10TT032', '9.4R53') where path LIKE '%003.10TT032.'

Jayasurya Satheesh Over a year ago

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

Arie Over a year ago

But all two first parts are the same within one product if you look at my example.

|

Pawan Kumar · Accepted Answer · 2018-01-08 12:44:14Z

1

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)

answered Jan 8, 2018 at 12:44

Pawan Kumar

2,02112 silver badges12 bronze badges

1 Comment

Arie Over a year ago

Hi, What about replace isn't enough as suggested above? I am not sure

Arulkumar · Accepted Answer · 2018-01-08 12:58:09Z

0

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

answered Jan 8, 2018 at 12:58

Arulkumar

13.3k14 gold badges51 silver badges76 bronze badges

Collectives™ on Stack Overflow

Replace specific strings in two columns

3 Answers 3

8 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related