Safe conversion from varchar to uniqueidentifier (SQL 2005)



  • Hey guys,

    I'm building a SQL script that migrates the data from an old database schema (really just a pile of vomit), massages and puts the data into a new, clean and normalized schema.  Now, along with broken or non-existent FK constraints and relationships, there is bad (read grandfathered) data mixed with newer, good data.  In particular there is an ID column, varchar(50), which can either contain a username string (bsmith233, which is useless and will be ignored), and guids, which I need and want for further processing and storage.  I should point out that the entire migration is a single batch script wrapped in a transaction, and each atomic step in the migration is wrapped in a try/catch which handles error information, etc.

    The problem is that I haven't found a way to do a "try-parse" on this data.  In know I can write a RegEx CLR UDF in .Net and call that from the script to test for a match before a convert, but that isn't possible in my case because the SQL server instances are locked down and they aren't keen on adding my custom assemblies to a shared production environment (well, at least not without a HUGE amount of red tape, etc.).

    I even tried calling it from within a nested try/catch block, but if the try block fails (which it will), it makes the entire transaction uncommitable and the rest of the batch is fucked...

    So if anybody has any idea on how to safely test and convert the data, I would owe you a beer and a large pile of chicken wings...


  • ♿ (Parody)

    If you've stopped the flow of bad data into the table (which it sounds like), then the bad data won't change between now and deployment.  And that means it's a known, scriptable quantity!

    SELECT [ID]
     INTO #crapdata_validIds
    FROM [crapdata]
    WHERE CASE [ID] 
          -- begin copy/paste code from a C# script
           WHEN 'bsmith123' THEN NULL
           WHEN 'gdavid321' THEN 'e6ad6c56-b5ab-4522-9475-4a49b4c83cce'
           --end
           ELSE CAST(ID AS UNIQUEIDENTIFIER) END IS NOT NULL

    While I generally prefer scotch, beer does go much better with chicken wings!



  • @Alex Papadimoulis said:

    If you've stopped the flow of bad data into the table (which it sounds like), then the bad data won't change between now and deployment.  And that means it's a known, scriptable quantity!

    SELECT [ID]
     INTO #crapdata_validIds
    FROM [crapdata]
    WHERE CASE [ID] 
          -- begin copy/paste code from a C# script
           WHEN 'bsmith123' THEN NULL
           WHEN 'gdavid321' THEN 'e6ad6c56-b5ab-4522-9475-4a49b4c83cce'
           --end
           ELSE CAST(ID AS UNIQUEIDENTIFIER) END IS NOT NULL

    While I generally prefer scotch, beer does go much better with chicken wings!

    Thanks Alex.  I thought of taking this approach as well, but it seemed inelegant.  But really, who cares for a write-once migration script.

    And you're right, beer is the right, and only choice for wings!



  • @Alex Papadimoulis said:

    -- begin copy/paste code from a C# script

    C# for that? Excel, people, Excel!


  • ♿ (Parody)

    @blakeyrat said:

    @Alex Papadimoulis said:
    -- begin copy/paste code from a C# script

    C# for that? Excel, people, Excel!

    Come on now, don't take away all coding from the project! We're programmers, not data entry people! We solve problems with code, not spreasheets!



  • @Alex Papadimoulis said:

    @blakeyrat said:

    @Alex Papadimoulis said:
    -- begin copy/paste code from a C# script

    C# for that? Excel, people, Excel!

    Come on now, don't take away all coding from the project! We're programmers, not data entry people! We solve problems with code, not spreasheets!

    Ok fair enough.

    ...

    But it would still be quicker in Excel.



  • @Alex Papadimoulis said:

    @blakeyrat said:
    @Alex Papadimoulis said:
    -- begin copy/paste code from a C# script
    C# for that? Excel, people, Excel!
    Come on now, don't take away all coding from the project! We're programmers, not data entry people! We solve problems with code, not spreasheets!

    Do it in Excel - with VBA!



  • @Scarlet Manuka said:

    Do it in Excel - with VBA!
    Thanks, I think I just threw up a little in my mouth...


Log in to reply