identify whther a string is a valid UID

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

identify whther a string is a valid UID

Author

Topic

aravindt77
Posting Yak Master

120 Posts

Posted - 2007-06-27 : 02:07:13

Hi All,

Warm Wishes !!!

is there any way to identify whther a string is a valid uniqueidentifier data type

Its Very Urgent

Plz give me a suggestion

Aravind

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-27 : 02:18:38

There can be other approaches

declare @u varchar(100)
set @u='90C16F1F-6A68-4190-9E26-AB947553EE0A'
select  
	case when @u like '%[A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9]-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]-[A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9]%' 
	and len(@u)=36 then 
	1 
else 
	0 
end

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-27 : 02:18:45

You can certainly do it using regular expression:

declare @a varchar(50)
 
set @a = '9DDEBD13-CA71-4C13-9D19-A09DDA174A8E' -- Valid

if @a like 
'[0-9abcdef][0-9abcdef][0-9abcdef][0-9abcdef][0-9abcdef][0-9abcdef][0-9abcdef][0-9abcdef]-[0-9abcdef][0-9abcdef][0-9abcdef][0-9abcdef]-[0-9abcdef][0-9abcdef][0-9abcdef][0-9abcdef]-[0-9abcdef][0-9abcdef][0-9abcdef][0-9abcdef]-[0-9abcdef][0-9abcdef][0-9abcdef][0-9abcdef][0-9abcdef][0-9abcdef][0-9abcdef][0-9abcdef][0-9abcdef][0-9abcdef][0-9abcdef][0-9abcdef]'
print 'Valid'
else
print 'Invalid'

set @a = '9DDEBG13-CA71-4C13-9D19-A09DDA174A8E' -- Invalid

if @a like 
'[0-9abcdef][0-9abcdef][0-9abcdef][0-9abcdef][0-9abcdef][0-9abcdef][0-9abcdef][0-9abcdef]-[0-9abcdef][0-9abcdef][0-9abcdef][0-9abcdef]-[0-9abcdef][0-9abcdef][0-9abcdef][0-9abcdef]-[0-9abcdef][0-9abcdef][0-9abcdef][0-9abcdef]-[0-9abcdef][0-9abcdef][0-9abcdef][0-9abcdef][0-9abcdef][0-9abcdef][0-9abcdef][0-9abcdef][0-9abcdef][0-9abcdef][0-9abcdef][0-9abcdef]'
print 'Valid'
else
print 'Invalid'

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Kristen
Test

22859 Posts

Posted - 2007-06-27 : 02:49:48

See also IsGUID function:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53803&whichpage=2#182741

Kristen

aravindt77
Posting Yak Master

120 Posts

Posted - 2007-06-27 : 03:25:52

Thnk you so much for your early response

Once again Thank you

Aravind

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-27 : 03:51:33

I think there is no need of checking len
My modifed query

declare @u varchar(100)
set @u='90C16F1F-6A68-4190-9E26-AB947553EE0A'
select  
	case when @u like '[A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9]-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]-[A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9]' 
	then 
	1 
else 
	0 
end

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-06-27 : 06:21:41

"I think there is no need of checking len"

My function is using length to allow for possible GUID that has no hyphens, and also for a GUID that has "{...}" - e.g. came from VBScript. And allowing for leading/trailing spaces.

But if that is not needed a simple RegEx will do the job.

A function can afford to centralise those sort of useful variations - more might be added in the future - e.g. an UpperCase option might be required on a case-sensitive database.

Kristen

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-06-27 : 10:08:44

madhivanan:

declare @u varchar(100)
set	@u = newid()
select  
	case when @u like '[A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9]-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]-[A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9]' 
	then 
	1 
else 
	0 
end

Returns 0....

e4 d5 xd5 Nf6

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-06-27 : 10:29:10

Here is my shot at this (and my first TRY/CATCH SQL as well...):

create procedure IsGUID(@TestGUID char(36), @ReturnValue bit OUTPUT)
as
begin
declare	@GUID uniqueidentifier

set	@ReturnValue = 1

begin try
	set	@GUID = @TestGUID
end try

begin catch
	set	@ReturnValue = 0
end catch	

end

To test:

declare	@ReturnValue bit
declare	@TestGUID char(36)
--Try a good GUID
set	@TestGUID = '31FD0BD5-9C77-402D-9993-4D5A35F3C491'
exec IsGUID @TestGUID, @ReturnValue OUTPUT
select @ReturnValue
--Try a bad GUID
set	@TestGUID = 'X1FD0BD5-9C77-402D-9993-4D5A35F3C491'
exec IsGUID @TestGUID, @ReturnValue OUTPUT
select @ReturnValue

Unfortunately, I can't roll this logic into a function. Trying to create this:

create function IsGUID(@TestGUID char(36))
returns bit
as
begin
declare	@ReturnValue bit
declare	@GUID uniqueidentifier

set	@ReturnValue = 1

begin try
	set	@GUID = @TestGUID
end try

begin catch
	set	@ReturnValue = 0
end catch	

return	@ReturnValue

end

...gives me this ugly error:

Invalid use of side-effecting or time-dependent operator in 'BEGIN TRY' within a function.

So, no TRY/CATCH operations allowed in user-defined functions? That is sad....

e4 d5 xd5 Nf6

Kristen
Test

22859 Posts

Posted - 2007-06-27 : 10:31:45

"create procedure IsGUID(@TestGUID char(36), @ReturnValue bit OUTPUT)"

I would favour having a GUID as the output. Casting it is probably the NextThingToDo, so outputting a GUID or NULL (rather than a BIT) seems like a sensible approach to me - given that you are doing the CAST anyway!

Kristen

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-06-27 : 10:47:53

And return what if the test fails? NULL?

e4 d5 xd5 Nf6

Kristen
Test

22859 Posts

Posted - 2007-06-27 : 10:52:59

Yup. The "Caller" can test for NULL of course, but as a bonus they get a GUID-datatype returned if the String is a valid GUID (and I'm supposing that 99% of the time the data will be valid and in those circumstances the IsGUID test is only being used as a "safety net")

Kristen

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-06-27 : 16:19:03

Here's one that checks with optional parens or braces:


[A-Z0-9]{8}-[A-Z0-9]{4}-[A-Z0-9]{4}-[A-Z0-9]{4}-[A-Z0-9]{12}|\([A-Z0-9]{8}-[A-Z0-9]{4}-[A-Z0-9]{4}-[A-Z0-9]{4}-[A-Z0-9]{12}\)|\{[A-Z0-9]{8}-[A-Z0-9]{4}-[A-Z0-9]{4}-[A-Z0-9]{4}-[A-Z0-9]{12}\}

Kristen
Test

22859 Posts

Posted - 2007-06-28 : 02:52:04

SQL Server doesn't [natively] support full RegEx like that though ...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-28 : 06:11:08

quote:

Originally posted by blindman

madhivanan:

declare @u varchar(100)
set	@u = newid()
select  
	case when @u like '[A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9]-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]-[A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9]' 
	then 
	1 
else 
	0 
end

Returns 0....

e4 d5 xd5 Nf6

Here is a corrected version


declare @u varchar(100)
set @u=newid()
select  
	case when @u like '[A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9]-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]-[A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9]' 
	then 
	1 
else 
	0 
end

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources