Updating a sub set of a substring

Hello Readers,

Today I had a huge task to do at work and I thought I would share to the world what I had to do to accomplish it.

So basically our forms are initially saved in the database as a blob field as I call it.
Let's say we have a box on the form called Name and another one called Date Of Birth.
That would be saved in the database in a column displayed as such as the following screen shot:


The problem is SQL and our software can only read the date field if the year is first in the listing.  Below i showing what is currently in the database and what it needs to be written as.

So how do we used these values in the database??  I scratched my head, but finally got it after doing a little reading and thinking back words.

So Let's start to set up test records in a temp table.




CREATE TABLE #Example (
        ID INT,
        DATA TEXT
  )

Insert values into the temp table:

INSERT INTO #Example( ID, DATA )
  VALUES  ( 1,'<ObjectData FName="Ryan" LName="Braun" DateOfBirth="11171983"></ObjectData>')INSERT INTO #Example( ID, DATA )
    VALUES  ( 2,'<ObjectData FName="John" LName="Axford" DateOfBirth="04011983"</ObjectData>')INSERT INTO #Example( ID, DATA )
    VALUES  ( 3,'<ObjectData FName="Corey" LName="Hart" DateOfBirth="03241982"</ObjectData>')INSERT INTO #Example( ID, DATA )
    VALUES  ( 4,'<ObjectData FName="Yovani" LName="Gallardo" DateOfBirth="02271986"</ObjectData>')
      
Let's query the table to see how it looks.

SELECT *
  FROM #Example

Now we get to get our hands dirty and start to think.

Where do we start?  Well I wanted to know the charindex or starting point of the DateofBirth (I need to find this since it is a dynamic spot and not always on the same spot).

SELECT id, data,
      CHARINDEX('DateOfBirth',CONVERT(VARCHAR(MAX),DATA))
  FROM #Example







Perfect now that we know the chardindex we can start to look for the next piece in our puzzle which is finding the original value of the date of birth field.

SELECT id, data,
      SUBSTRING(CONVERT(VARCHAR(MAX),DATA),(CHARINDEX('DateOfBirth',CONVERT(VARCHAR(MAX),DATA))+13),8)
  FROM #Example

Awesome we know a very import part of this entire process.  Now we want to format the original date of birth correctly so for example 11171983 should be shown as 19831117.

SELECT id, data,
      SUBSTRING(SUBSTRING(CONVERT(VARCHAR(MAX),DATA),(CHARINDEX('DateOfBirth',CONVERT(VARCHAR(MAX),DATA))+13),8),5,4) + SUBSTRING(SUBSTRING(CONVERT(VARCHAR(MAX),DATA),(CHARINDEX('DateOfBirth',CONVERT(VARCHAR(MAX),DATA))+13),8),0,5)
  FROM #Example









Sweet!  We have both the original and new values.  Let's quick compare them so you can easily see the difference.

SELECT id, data,
      SUBSTRING(CONVERT(VARCHAR(MAX),DATA),(CHARINDEX('DateOfBirth',CONVERT(VARCHAR(MAX),DATA))+13),8) AS Original,
      SUBSTRING(SUBSTRING(CONVERT(VARCHAR(MAX),DATA),(CHARINDEX('DateOfBirth',CONVERT(VARCHAR(MAX),DATA))+13),8),5,4) + SUBSTRING(SUBSTRING(CONVERT(VARCHAR(MAX),DATA),(CHARINDEX('DateOfBirth',CONVERT(VARCHAR(MAX),DATA))+13),8),0,5) AS New
  FROM #Example

Now what is next?  Let's format the Original and new columns so we can use them with a replace function to easily update the values.

SELECT id, data,
  --original substring
      SUBSTRING(CONVERT(VARCHAR(MAX),DATA),CHARINDEX('DateOfBirth',CONVERT(VARCHAR(MAX),DATA)),22) AS Original,
  --new string
  'DateOfBirth="'+SUBSTRING(SUBSTRING(CONVERT(VARCHAR(MAX),DATA),(CHARINDEX('DateOfBirth',CONVERT(VARCHAR(MAX),DATA))+13),8),5,4) + SUBSTRING(SUBSTRING(CONVERT(VARCHAR(MAX),DATA),(CHARINDEX('DateOfBirth',CONVERT(VARCHAR(MAX),DATA))+13),8),0,5)+'"' AS New
  FROM #Example
      

Now lets just finish this with the replace and update commands.

UPDATE #Example SET DATA = REPLACE(CONVERT(VARCHAR(MAX),a.DATA),Original,New)
  FROM #Example a
      INNER JOIN (
  SELECT id, data,
    --original substring
        SUBSTRING(CONVERT(VARCHAR(MAX),DATA),CHARINDEX('DateOfBirth',CONVERT(VARCHAR(MAX),DATA)),22) AS Original,
    --new string
    'DateOfBirth="'+SUBSTRING(SUBSTRING(CONVERT(VARCHAR(MAX),DATA),(CHARINDEX('DateOfBirth',CONVERT(VARCHAR(MAX),DATA))+13),8),5,4) + SUBSTRING(SUBSTRING(CONVERT(VARCHAR(MAX),DATA),(CHARINDEX('DateOfBirth',CONVERT(VARCHAR(MAX),DATA))+13),8),0,5)+'"' AS New
    FROM #Example
  )b ON a.id = b.id


Now let's double check but query the original temp table to insure the update worked.
SELECT *
  FROM #Example

Congrats we did it! Another great example of the power of SQL.  Hopefully you learned something new as I did too.

I will leave you with this little count down before signing off; its only 88 days until Spring Training! Who's excited for some baseball??!?!

Cheers!

Robert