How to replace a part of string

Question

I want to update the substring between the two first successive parameter I want to write a query which will do the same effect as the example below , johnold is just an example

a= 'parameterjohnoldparameter123parameter'
b='aaa'

result would be

a= 'parameteraaaparameter123parameter'

How can I do something like that ?

Is there any text before the first `parameter` or will the first value always be found at position 10?. — Mikael Eriksson, Dec 20 '13 at 15:41

score 3 · Accepted Answer · answered Dec 20 '13 at 15:32

3

You can stuff it in there.

declare @a varchar(30)
declare @b varchar(30)

set @a = 'parameterjohnoldparameter123'
set @b = 'aaa'

select stuff(@a, 10, 7, @b)
-- or do a search for the second parameter string
select stuff(@a, 10, charindex('parameter', @a, 10) - 10, @b)

answered Dec 20 '13 at 15:32

Mikael Eriksson

136,425
22
210
281

+1 Better than my answer, assuming the part to be replaced works on ordinal position and is fixed length rather than being a specific value. – Bridge Dec 20 '13 at 15:35

score 1 · Answer 2 · answered Dec 20 '13 at 15:30

1

I guess you're looking for:

SET a = REPLACE(a, 'johnold', b)

But how do we know what the part of the string you want to replace in the first string is? I've just guessed based on your example.

answered Dec 20 '13 at 15:30

Bridge

29,818
9
60
82

score 0 · Answer 3 · answered Dec 20 '13 at 15:31

0

To change in a single table

 UPDATE `table_name`
 SET `field_name` = replace(same_field_name, 'unwanted_text', 'wanted_text')

answered Dec 20 '13 at 15:31

Filip Huysmans

1,301
1
20
44

score 0 · Answer 4 · answered Dec 20 '13 at 15:32

0

From: http://technet.microsoft.com/en-us/library/ms186862.aspx

REPLACE( string_expression , string_pattern , string_replacement )

example:

SELECT REPLACE('Hello World', 'Hello', 'Goodbye Cruel')
GO

answered Dec 20 '13 at 15:32

Ezra Bailey

1,434
13
25

Alex Peshik · Answer 5 · 2013-12-20T16:21:11.987

0

To avoid using fixed length or searching parameters you can use:

declare @a varchar(max),@b varchar(max),@c varchar(max)

set @a = 'parameterjohnoldparameter123parameter' --original row
set @b = 'aaa' --value to insert
set @c = 'parameter' --value to insert after it

select stuff(@a, LEN(@c)+1, charindex(@c, @a, LEN(@c)+1) - LEN(@c)-1, @b)

Or even calculate LEN(@c) one time before select.

edited Dec 20 '13 at 16:21

answered Dec 20 '13 at 16:16

Alex Peshik

1,515
2
15
20

Because we don't know max length of each parameter: a,b,c. I used "max", but if author knows the real max value (for example, replaceable part is from some column varchar(100)), we can use exact value. I understand that "max" isn't good idea for performance. – Alex Peshik Dec 21 '13 at 06:28

How to replace a part of string

5 Answers5