I've been cracking my brain on a problem that I had at work. In one of my tables is a bunch of rows, with a certain column that contains XML data that has several properties like this: (simplified)
<file data_first="1" data_second="2">
Now the problem is that some of the properties in columns are not in lowercase, causing some problems in the software:
<file Data_First="1" Data_Second="2"> <file dATa_THird="3" DATA_FoURTH="4">
Simply put, I want to do the following for all columns:
- Iterate through these rows
- Find strings that match "%data_%=" in any case
Replace that part of the column with it's lowercase counterpart so the above XML's properties become:
I tried a bunch of things, but ultimately didn't get too far. Hopefully this is in the right direction:
UPDATE tablename SET xmlcolumnname = REPLACE(xmlcolumnname, SUBSTR('', ), BINARY LOWER(SUBSTR()));
I added the BINARY to make sure it's inserted with case sensitivity intact.
Thank you very much for your time.