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 ?
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)
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.
To change in a single table
UPDATE `table_name`
SET `field_name` = replace(same_field_name, 'unwanted_text', 'wanted_text')
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
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.
parameteror will the first value always be found at position 10?.