Sql function for finding a guid

0

What I’m trying to do is search through a specific column in my database and pull out any rows that match a pattern (i.e. any rows where the nvarchar contains a guid).

I’ve got this function.

Create FUNCTION [dbo].[HasGuid] (@Data varchar(50)) RETURNS bit WITH SCHEMABINDING AS BEGIN return case when @Data like REPLACE('%00000000-0000-0000-0000-000000000000%', '0', '[0-9a-fA-F]') then 1 else 0 end END

It works on most of my data

values it works on:

12341234-1234-1234-1234-123412341234.ext
sometext:12341234-1234-1234-1234-123412341234:c:path341234-1234-1234-1234-123412341234341234-1234-1234-1234-123412341234.ext
sometext:12341234-1234-1234-1234-123412341234.ext

values it doesn’t work on:

c:path341234-1234-1234-1234-123412341234341234-1234-1234-1234-123412341234.ext

Where .ext is one of any number of different extensions and the 1234 guid is just representative and not actually the same guid multiple times in any one string.

And I’m using the function like so (should be obvious, but just to be complete)

SELECT * from SomeTable s
WHERE dbo.HasGuid(s.SomeColumn) = 0

At this point I would expect to see only the rows where that column is not a guid but I’m getting the discrepancy above. I’m not seeing what’s wrong in my function, but if someone could point out what I’m missing it’d be super helpful.

Link: Sql function for finding a guid
Source: Stack Sql

Share.

About Author

Leave A Reply