How to Find and Replace in Update Query ?
For e.g. I have field name orderFreQuencyvalues in tbl_OrderFrequency_mst
This Field contains values like ’04:30,05:00,05:30,06:00′ i.e coma separated values in one field of a table.
Now, how do I replace ’06:00′ with ’06:30′ in a query ? Let do it here.
Declare the Variables.
DECLARE @find varchar(255),
@replace varchar(255),
@patfind varchar(255)
Initialize the values for the variables.
SELECT @find = ’06:00′,
@replace = ’06:30′
SELECT @patfind = ‘%’ + @find + ‘%’
Run the below query.
UPDATE tbl_OrderFrequency_mst
SET orderFreQuencyvalues = STUFF( orderFreQuencyvalues,
PATINDEX( @patfind, orderFreQuencyvalues ),
DATALENGTH( @find ),
@replace )
WHERE orderFreQuencyvalues LIKE @patfind
STUFF( text or Field Name , start , length , Replace value)
Purpose of STUFF:
Deletes a specified length of characters and inserts another set of characters at a specified starting point
Patindex( FindValue , text or Field Name)
Find the Index where Findvalue Start
Datalength( TextData )
Return the lenght of the text
I hope you find this useful somewhere.