Detecting if UniqueIdentifier

  • We are using a table that stores text values (like 'Abc') and uniqueidentifiers in the same NVarchar(4000) field.   For example

    id        value

    1         abc

    2         cde

    3         4A4E727D-EFB5-454E-8431-34BE896E7420

    4         C391FAAC-AE40-49F9-9155-B75C9DC6E2F

    5         124

    I need to write a query that returns rows 3 and 4 by detecting that a GUID is stored in the value column.  Is there a quick and easy way to do this?

  • Hi Brian,

    Sounds like you might consider normalizing the database a bit more.  But you might try something like this:

    Here you are assuming that the only values in the column that are 36 characters long are UIDs.

    Select *

    FROM table t

    WHERE len(t.field) = 36

     

    Another approach might be to parse the value of each record and look for the dashes using charindex(). 

  • Regretfully, the business requirements don't allow me to denormalize. 

    Anyways, that statement would only work if I could ensure that the text values were never the same length as a guid.

  • Since the uniqueidentifiers always have the same format you should be able to use that to quickly look up those values.

    IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id('tempdb..#Test'))

        DROP TABLE #Test

    CREATE TABLE #Test

        (

        id int identity(1,1)

        ,Value nvarchar(50)

        )

    INSERT INTO #Test(Value) VALUES('F7014BA8-9C9B-4AB4-A62D-80D957D2C4B3')

    INSERT INTO #Test(Value) VALUES('B63C44C4-5798-450E-B0BC-E0A6AD1F0561')

    INSERT INTO #Test(Value) VALUES('8E002ECF-C069-4EA0-8AD2-506290FA786E')

    INSERT INTO #Test(Value) VALUES('4329E8BD-7E5F-45C6-A1E3-DD4ED91CB7BB')

    INSERT INTO #Test(Value) VALUES('B42FF1385956')

    INSERT INTO #Test(Value) VALUES('4B81C3C82851')

    INSERT INTO #Test(Value) VALUES('4345F109749CDB5F')

    SELECT * FROM #Test

    SELECT * FROM #Test

    WHERE SUBSTRING(Value,9,1) = '-'

        AND SUBSTRING(Value,14,1) = '-'

        AND SUBSTRING(Value,19,1) = '-'

        AND SUBSTRING(Value,24,1) = '-'

     




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • Either you can wrap in a function like so (especially if using SQL 2K)

     

    CREATE FUNCTION dbo.Is_Uniqueidentifier (@valCheck VARCHAR(36))

    RETURNS bit

    AS

    BEGIN

    DECLARE @outVal AS bit

    -- Return 1 for true

    -- Return 0 for false

    -- Use UPPER in case system is case sensitive.

    -- Pattern match FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFF

    IF UPPER(@valCheck) LIKE '[A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9]-[A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9]-[A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9]-[A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9]-[A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9]'

     SET @outVal = 1

    ELSE

     SET @outVal = 0

     RETURN (@outVal)

    END

     

    Or if using SQL 7 or don't want to create a function then add to your where clause

     

    SELECT * FROM tblX WHERE

     UPPER(value) LIKE '[A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9]-[A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9]-[A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9]-[A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9]-[A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9]'

     

    This should help.

    The problem with the previous is it doesn't check for invalid characters in the other spots.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply