Testing that a varchar could be converted to a uniqueidentifier

• Jesse Reich

  Hall of Fame

  Points: 3020

  More actions

  June 29, 2012 at 4:35 pm

  #277141

  I have a legacy stored procedure which tests an ID of type varchar against a table where the ID is a uniqueidentifer:

  CREATE PROC p_Check (@id varchar(50))

  AS

  BEGIN

  /*

  Schema for T1

  -------------------------------------------

  u_id UNIQUEIDENTIFER NOT NULL PRIMARY KEY

  u_val VARCHAR(50) NOT NULL

  */

  SELECT *

  FROM T1

  WHERE CONVERT(VARCHAR(50), u_id) = @id

  /*

  Behavior: if the u_id does not match

  then return nothing

  */

  END

  I cannot change the parameter being sent (legacy application) so I'm trying to think of a way to test that the varchar is a valid uniqueidentifier or return nothing if it isn't.

  CREATE PROC p_Check (@id varchar(50)) AS

  BEGIN

  DECLARE @U UNIQUEIDENTIFIER

  BEGIN TRY

  SET @U = CAST(@id AS UNIQUEIDENTIFIER)

  END TRY

  BEGIN CATCH

  SET @U = NULL

  END

  SELECT *

  FROM T1

  WHERE u_id = @id

  END

  Is this safe? Is there a more efficient way to achieve the same thing?

• David Betteridge

  Hall of Fame

  Points: 3264

  More actions

  June 29, 2012 at 4:50 pm

  #1507376

  Not sure it's any better but you could try a like statement.

  if '72E28A38-8A55-4F54-AE61-F870BA58E9FD' like '[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]-[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]-[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]-[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]-[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]' print 'match'

  regards

  David

• Jesse Reich

  Hall of Fame

  Points: 3020

  More actions

  June 29, 2012 at 5:04 pm

  #1507380

  Thanks, David.

  Do you have a preference of one over the other?

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  June 29, 2012 at 5:24 pm

  #1507381

  I would invlove an Regex-powered CLR for this task. Simple, powerfull and nasty fast.

• Gullimeel

  Hall of Fame

  Points: 3871

  More actions

  July 1, 2012 at 4:46 am

  #1507529

  Your approch is just fine But I will not use a new varaible but rather will use the select statement in try

  drop table T1

  go

  create table T1

  (

  u_id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,

  u_val VARCHAR(50) NOT NULL

  )

  go

  insert into T1 (u_id,u_val)

  select newid(),'1'

  insert into T1 (u_id,u_val)

  select newid(),'2'

  insert into T1 (u_id,u_val)

  select newid(),'3'

  go

  drop proc p_check

  go

  CREATE PROC p_Check (@id varchar(50)) AS

  BEGIN

  --DECLARE @U UNIQUEIDENTIFIER

  BEGIN TRY

  SELECT *

  FROM T1

  WHERE u_id = cast (@id as uniqueidentifier)

  END TRY

  BEGIN CATCH

  set @id = null

  END Catch

  END

  go

  declare @u_id varchar(50)

  select @u_id =u_id from T1 where u_val = 1

  exec p_Check @id = @u_id

  --now change the value of @u_id to make it pure varchar

  set @u_id = REPLACE(@u_id,'-','')

  --call the proc again..Inout is bad and is not true uniqueidentifier ..You will get empty resultset

  exec p_Check @id = @u_id

  GulliMeel

  Finding top n Worst Performing queries[/url]
  Improve the performance of Merge Join(special case)
  How to Post Performance Problem -Gail Shaw[/url]

• Jeff Moden

  SSC Guru

  Points: 1004290

  More actions

  July 1, 2012 at 10:56 am

  #1507549

  ColdCoffee (6/29/2012)

  ---

  I would invlove an Regex-powered CLR for this task. Simple, powerfull and nasty fast.

  If it were me, I wouldn't bother with the CLR for this task because the LIKE works just fine here. In fact, for such simple things, it's very likely that LIKE will beat Regex for performance.

  If you'd (no pun intended) like, build a CLR for this, gen it as a script, and I'll setup a nice little million row test to find out for sure.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jeff Moden

  SSC Guru

  Points: 1004290

  More actions

  July 1, 2012 at 11:00 am

  #1507550

  David Betteridge (6/29/2012)

  ---

  Not sure it's any better but you could try a like statement.

  if '72E28A38-8A55-4F54-AE61-F870BA58E9FD' like '[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]-[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]-[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]-[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]-[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]' print 'match'

  regards

  David

  Absolutely the right idea! You just need to make one little change. The letters in a GUID only go from A to F.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Gullimeel

  Hall of Fame

  Points: 3871

  More actions

  July 1, 2012 at 11:03 am

  #1507551

  Either I have not understood what the original OP wanted but why do we need a like statement when the sql server provides us with the method which can convert a varchar value ( provided it is formatted properly) to the uniqueidentifier value. That is more readable and should be able to use the clustered index seek as well.

  GulliMeel

  Finding top n Worst Performing queries[/url]
  Improve the performance of Merge Join(special case)
  How to Post Performance Problem -Gail Shaw[/url]

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  July 1, 2012 at 11:17 am

  #1507552

  Jeff Moden (7/1/2012)

  ---

  ColdCoffee (6/29/2012)

  ---

  I would invlove an Regex-powered CLR for this task. Simple, powerfull and nasty fast.

  If it were me, I wouldn't bother with the CLR for this task because the LIKE works just fine here. In fact, for such simple things, it's very likely that LIKE will beat Regex for performance.

  If you'd (no pun intended) like, build a CLR for this, gen it as a script, and I'll setup a nice little million row test to find out for sure.

  Sure Jeff, tomorrow night; i dont have time until tomorrow night to do this anyways (and you know the reason, please refer my mail 🙂 )

• Jeff Moden

  SSC Guru

  Points: 1004290

  More actions

  July 1, 2012 at 10:18 pm

  #1507593

  Gullimeel (7/1/2012)

  ---

  Either I have not understood what the original OP wanted but why do we need a like statement when the sql server provides us with the method which can convert a varchar value ( provided it is formatted properly) to the uniqueidentifier value. That is more readable and should be able to use the clustered index seek as well.

  That's the whole point. The goal is to deterimine if the VARCHAR value provided is, in fact, a GUID.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• David Betteridge

  Hall of Fame

  Points: 3264

  More actions

  July 2, 2012 at 1:43 am

  #1507629

  Absolutely the right idea! You just need to make one little change. The letters in a GUID only go from A to F.

  good point! 🙂

• Jeff Moden

  SSC Guru

  Points: 1004290

  More actions

  July 2, 2012 at 3:49 pm

  #1507952

  I've not tested it for performance but it is an iSF (Inline Scalar Function). These are typically 8 to 10 times faster than an equivalent Scalar Function. It should even beat a CLR but, since I can't even spell "C#", I'll have to wait for someone to write one and gen the T-SQL script to build it before I can prove that or not.

  Examples of how to use this function, which checks to see if a string is in the correct form of a GUID, are in the "Example Usage" sections in the code below. And, Yes, I know... I named the function and the column it returns by the same name.

  CREATE FUNCTION dbo.IsGUID

  /*******************************************************************************

  Purpose:

  If the given string parameter matches the format of GUID, return a 1.

  Otherwise, return a 0.

  Notes:

  1. This is what Microsoft refers to as an "Inline Scalar Function" and it's

  MUCH faster than a regular or classic Scalar Function.

  Example Batch Usage:

  SELECT st.SomeVarcharColumn,

  ig.IsGUID

  FROM dbo.SomeTable st

  CROSS APPLY dbo.IsGUID(st.SomeVarcharColume) ig

  Example Single Variable Usage:

  SELECT IsGUID

  FROM dbo.IsGUID(@SomeString)

  Revision History:

  Rev 00 - 2 Jul 2012 - Jeff Moden - Initial creation

  Ref: http://www.sqlservercentral.com/Forums/Topic1323353-392-1.aspx

  *******************************************************************************/

  (@pSomeString VARCHAR(100))

  RETURNS TABLE WITH SCHEMABINDING AS

  RETURN

  SELECT IsGuid = CASE

  WHEN @pSomeString LIKE '[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]'

  THEN 1

  ELSE 0

  END

  ;

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• David Betteridge

  Hall of Fame

  Points: 3264

  More actions

  July 2, 2012 at 4:35 pm

  #1507967

  CLR version below and attached ( i hope!)

  CREATE ASSEMBLY [IsGUID]

  AUTHORIZATION [dbo]

  FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103002321F24F0000000000000000E00002210B010800000A000000060000000000000E290000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000B428000057000000004000004003000000000000000000000000000000000000006000000C000000402800001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E746578740000001409000000200000000A000000020000000000000000000000000000200000602E72737263000000400300000040000000040000000C0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001000000000000000000000000000004000004200000000000000000000000000000000F028000000000000480000000200050074210000CC060000010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000013300200180100000100001100170A0F00FE16030000016F1100000A0B0772010000706F1200000A130511052D0C720100007007281300000A0B0772050000706F1400000A130511052D0C077205000070281300000A0B076F1500000A1F26FE01130511052D0900160A0038A700000000160C38900000000007086F1600000A0D08130611061F0E30131106162E2211061F092E2B11061F0E2E252B4111061F132E1D11061F182E1711061F252E202B2D091F7BFE01130511052D02160A2B43091F2DFE01130511052D02160A2B34091F7DFE01130511052D02160A2B2509281700000A2D12091F41320A091F46FE0216FE012B01162B0117130511052D02160A2B00000817580C081F26FE04130511053A62FFFFFF0006281800000A13042B0011042A42534A4201000100000000000C00000076322E302E35303732370000000005006C00000024020000237E0000900200000C03000023537472696E6773000000009C0500000C00000023555300A8050000100000002347554944000000B80500001401000023426C6F620000000000000002000001471502000900000000FA0133001600000100000016000000020000000100000001000000180000000E00000001000000010000000200000000000A00010000000000060033002C000A005B0046000A00660046000600960084000600B30084000600D00084000600EF00840006000801840006002101840006003C01840006005701840006008F0170010600A30170010600B10184000600CA0184000600FA01E70143000E02000006003D021D0206005D021D020A009D0282020600BB022C000600F2022C0000000000010000000000010001008101100015000000050001000100502000000000960070000A0001000000010077002100AD0011002900AD0011003100AD0011003900AD0011004100AD0011004900AD0011005100AD0011005900AD0011006100AD0016006900AD0011007100AD0011007900AD0011008100AD001B009100AD0021009900AD002600A100AD0026000900B2022F00A900C2023300A900CD023800A900D4023300A900DD023E00A900E8024200B100F7024700110000034C00200083002A002E00330094002E0013006E002E001B006E002E00230074002E002B008A002E000B005D002E003B006E002E004B006E002E005300AC002E006300D6002E006B00E3002E007300EC002E007B00F50052000480000001000000000000000000000000007B020000020000000000000000000000010023000000000002000000000000000000000001003A00000000000000003C4D6F64756C653E004973475549442E646C6C004775696446756E6374696F6E73006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C426F6F6C65616E0053716C537472696E670049734775696400706F737369626C65475549440053797374656D2E5265666C656374696F6E00417373656D626C795469746C65417474726962757465002E63746F7200417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C65417474726962757465004775696441747472696275746500417373656D626C7956657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C69747941747472696275746500497347554944004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E41747472696275746500546F537472696E6700537472696E67005374617274735769746800436F6E63617400456E647357697468006765745F4C656E677468006765745F436861727300436861720049734E756D626572006F705F496D706C696369740000037B0000037D0000000000752A02430862FA4783ABD7085DD16F3F0008B77A5C561934E0890600011109110D042001010E042001010205200101114504200101080320000104010000000320000E042001020E0500020E0E0E03200008042001030804000102030500011109020A0707020E0803110902081001000B49734755494420546573740000050100000000150100104461766964204265747465726964676500000901000454657374000017010012436F7079726967687420C2A920203230313200002901002462633835333634662D373034322D343534302D396330372D61616632376330323264333600000C010007312E302E302E3000000801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000002321F24F0000000002000000580000005C2800005C0A00005253445315F7FB80F10E0D4CA9E5A61F15E1F11202000000433A5C506572736F6E616C5C446176696420426574746572696467655C4973475549445C4973475549445C6F626A5C44656275675C4973475549442E70646200DC2800000000000000000000FE280000002000000000000000000000000000000000000000000000F02800000000000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500204000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000E80200000000000000000000E80234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00448020000010053007400720069006E006700460069006C00650049006E0066006F00000024020000010030003000300030003000340062003000000044001100010043006F006D00700061006E0079004E0061006D0065000000000044006100760069006400200042006500740074006500720069006400670065000000000040000C000100460069006C0065004400650073006300720069007000740069006F006E0000000000490073004700550049004400200054006500730074000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E003000000038000B00010049006E007400650072006E0061006C004E0061006D00650000004900730047005500490044002E0064006C006C00000000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A900200020003200300031003200000040000B0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000004900730047005500490044002E0064006C006C00000000002C0005000100500072006F0064007500630074004E0061006D00650000000000540065007300740000000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000103900000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

  WITH PERMISSION_SET = SAFE

  GO

  CREATE FUNCTION IsGUID(@possibleGUID nvarchar(4000)) RETURNS bit

  AS EXTERNAL NAME IsGUID.GuidFunctions.IsGuid;

  GO

  SELECT dbo.IsGUID('not a guid')

  GO

  SELECT dbo.IsGUID('{66D2701F-874C-4055-A28C-76D7E1FAAF69}');

  GO

  SELECT dbo.IsGUID('66D2701F-874C-4055-A28C-76D7E1FAAF69');

  GO

• Gullimeel

  Hall of Fame

  Points: 3871

  More actions

  July 3, 2012 at 2:52 am

  #1508077

  @jeff

  I would add following.This will be more helpful if most of my rows are not guid.But if most of them are GUID the I might stick with what you have posted..

  Did you try to use the convert(uniqueidentifier) with begin try etc.? I know you wont be able to use that as in line function..

  SELECT IsGuid = CASE

  when (len(@guid) <> 36 or LEN(replace(@guid,'-','')) <> 32 or PATINDEX('%[G-Z]%',@guid) <> 0) then 0

  WHEN @GUID LIKE '[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]'

  THEN 1

  ELSE 0

  END

  GulliMeel

  Finding top n Worst Performing queries[/url]
  Improve the performance of Merge Join(special case)
  How to Post Performance Problem -Gail Shaw[/url]

• Jeff Moden

  SSC Guru

  Points: 1004290

  More actions

  July 3, 2012 at 5:24 am

  #1508160

  David Betteridge (7/2/2012)

  ---

  CLR version below and attached ( i hope!)

  Awesome. Thanks David. I'll setup a test tonight.

  As a sidebar, would you mind editing your post to put the code into the [ code = "sql" ] IFCode shortcut tags so that your post doesn't keep the width of further responses at a very wide level? Just click on the EDIT button at the top right of your post, highlight your code, and then click on the [ code = "sql" ] entry of the IFCode shortcuts that are to the left of the edit window.

  Thanks, again.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)