1

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:

    data_first="1" data_second="2"

    data_third="3" data_fourth="4"

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.

Improve this question
2
  • 1
    I'll try that again - I think REPLACE(haystack, 'needle', 'pin') performs case-sensitive seek i.e. won't find 'Needle' - are you dealing with that? Other tools are INSTR() and STUFF(). If necessary, you could cast a copy of the entire field uppercase, find each instance of the property name in that version, and Stuff the correct case version into the original actual string, where it may have been correct anyway. Easier if the property name only appears once in the XML data in one row, but in general I think that isn't guaranteed.
    – Robert Carnegie
    Jul 10, 2017 at 23:22
  • I didn't manage to do it with a SQL query, but I did apply some of your suggestions to a PHP script instead. Thank you for the help!
    – Nick
    Jul 11, 2017 at 11:46

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.