Filter field containing uniqueidentifiers & names

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)

Filter field containing uniqueidentifiers & names

Author

Topic

Shamee_1321
Starting Member

15 Posts

Posted - 2008-02-15 : 02:57:54

Hello,

I have a field named AUTOR(nvarchar(101)) in table TRUCOS.. The thing is that this fields stores either the name of a person or the userid(uniqueidentifier) of the person.

How can I select only the fields where there are names??
How can I select only the fields where there are userids???

drampall
Starting Member

1 Post

Posted - 2008-02-15 : 03:10:49

Try this :

SELECT count(1)
FROM ITGC_ES.dbo.[aspnet_Membership]
WHERE (CHARINDEX('-', userid) > 1) AND (LEN(userid) = 36)

This should give you only GUIDs except if a name has a '-' and is 32 chars in lenght.

Shamee_1321
Starting Member

15 Posts

Posted - 2008-02-15 : 03:19:31

Thanks.. :)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-02-15 : 14:14:49

Maybe this might help? (as a sample, I didn't use your supplied object names)

SELECT
	CASE
		WHEN MyColmn 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 AS IsGuid
FROM MyTable

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-02-15 : 14:42:18

Have you considered storing the data in different columns with the correct data types?

How do you know that my name isn't 'D30EE6E6-462A-4B00-921E-FCEA5BB199AB'?

CODO ERGO SUM

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-15 : 15:23:25

quote:
Originally posted by Michael Valentine Jones

How do you know that my name isn't 'D30EE6E6-462A-4B00-921E-FCEA5BB199AB'?

Is that pronounced "Colonel"?

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources