For every row where there is html text in a given column I need to do the following. I need to replace a string WITH whatever the strings that follow have as text. While I know that in SQL I can do something like this
SELECT REPLACE('I like cats', 'cat', 'dog');
as I indicate, I know that I have to replace the string cat in the chain "I like cats" but it is not a fixed word like "dog", it will be different for every row, so I wonder if I can sort of parse it, fetch whatever stands instead of dog and replace the word cat in that "chain".
To be more precise, this is a real cut out of our text:
<h2 title="TEXT TO BE REPLACED"><ins>This is the text that should replace it</ins></h2>
It seems to me this can’t be done with SQL alone. It would instead be more appropriate to export the whole thing to a txt file and use python to do the replacements?
Replace a string inside a list of strings with a variable string for every row
>Solution :
Yes, it is possible to parse HTML text and replace strings with variable strings for every row using SQL, but it would be complex and require multiple steps. It would be easier to export the HTML text to a text file and use Python to perform the replacements.
def replace_string(list_of_strings, old_string, new_string_generator):
"""Replaces a string inside a list of strings with a variable string for every row.
Args:
list_of_strings: A list of strings.
old_string: The string to be replaced.
new_string_generator: A function that takes a row number as input and returns
the new string to replace the old string with.
Returns:
A list of strings with the old string replaced with the new string.
"""
new_list_of_strings = []
for row_number, row_string in enumerate(list_of_strings):
new_string = new_string_generator(row_number)
new_list_of_strings.append(row_string.replace(old_string, new_string))
return new_list_of_strings
# Example usage:
list_of_strings = ["I like cats", "I like dogs", "I like birds"]
old_string = "like"
new_string_generator = lambda row_number: "love" if row_number % 2 == 0 else "hate"
new_list_of_strings = replace_string(list_of_strings, old_string, new_string_generator)
print(new_list_of_strings)
Output:
['I love cats', 'I hate dogs', 'I love birds']
To use this code to replace strings in a column of HTML text, you would first need to export the HTML text to a text file. Then, you could use the replace_string() function to replace the strings in the text file. Finally, you could import the modified text file back into your database.