May 6, 2004 at 10:52 am
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?
May 6, 2004 at 12:30 pm
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().
May 6, 2004 at 12:33 pm
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.
May 6, 2004 at 1:12 pm
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.
May 6, 2004 at 1:38 pm
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