Menu
I basically have an xml column, and I need to find and replace one tag value in each record.
642
On the Edit menu, point to Find and Replace, and then click Quick Replace to open the dialog box with both find options and replace options. Toolbar buttons and shortcut keys are also available to open the Find and Replace dialog box.
The basic syntax of replace in SQL is: REPLACE(String, Old_substring, New_substring); In the syntax above: String: It is the expression or the string on which you want the replace() function to operate.
If you'd like to replace a substring with another string, simply use the REPLACE function. This function takes three arguments: The string to change (which in our case was a column). The substring to replace.
SELECT REPLACE(REPLACE(REPLACE(REPLACE('3*[4+5]/{6-8}', '[', '('), ']', ')'), '{', '('), '}', ')'); We can see that the REPLACE function is nested and it is called multiple times to replace the corresponding string as per the defined positional values within the SQL REPLACE function.
For anything real, I'd go with xpaths, but sometimes you just need a quick and dirty solution:
You can use CAST to turn that xml column into a regular varchar, and then do your normal replace.
UPDATE xmlTable SET xmlCol = REPLACE( CAST( xmlCol as varchar(max) ), '[search]', '[replace]')
That same technique also makes searching XML a snap when you need to just run a quick query to find something, and don't want to deal with xpaths.
SELECT * FROM xmlTable WHERE CAST( xmlCol as varchar(max) ) LIKE '%found it!%'
Edit: Just want to update this a bit, if you get a message along the lines of Conversion of one or more characters from XML to target collation impossible, then you only need to use nvarchar which supports unicode.
CAST( xmlCol as nvarchar(max) )
123
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
