CSV table - SQL Server Forums

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)

CSV table

Author

Topic

GoDaddy
Yak Posting Veteran

64 Posts

Posted - 2009-06-23 : 14:07:38

I have this function that do a comma seperated value table ... I want to add the support of uniqueIdentifier. How can I detect that the value is a uniqueIdentifier. SQL has built in function to detect if it's numeric, a date ... but i can't seem to find something for detecting a unique identifier

Here is my code

ALTER FUNCTION [dbo].[CSVTable] ( @Str varchar(7000) )
RETURNS @t TABLE (numberVal int, stringVal varchar(7000), dateVal datetime, guidVal uniqueIdentifier)
AS
BEGIN

DECLARE @i int;
DECLARE @c varchar(1000);

SET @Str = @Str + ','
SET @i = 1;
SET @c = '';

WHILE @i <= len(@Str)
BEGIN
IF SUBSTRING(@Str,@i,1) = ','
BEGIN
INSERT INTO @t
VALUES (CASE WHEN ISNUMERIC(@c)=1 THEN @c else Null END
, RTRIM(LTRIM(@c))
, CASE WHEN ISDATE(@c)=1 then @c else Null END)
SET @c = ''
END
ELSE
SET @c = @c + SUBSTRING(@Str,@i,1)
SET @i = @i +1
END
RETURN
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 14:22:00

You can check the string representation with

@var 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]'

or similar...

E 12°55'05.63"
N 56°04'39.26"

GoDaddy
Yak Posting Veteran

64 Posts

Posted - 2009-06-23 : 14:38:29

Maybe trying to convert to uniqueIdentifier and wrapping it with a try/catch .... ? what do you think

GoDaddy
Yak Posting Veteran

64 Posts

Posted - 2009-06-23 : 14:44:41

ah damn .. i can't use a try/catch block in a user define function ... is there a workaround for this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 14:49:26

CREATE FUNCTION dbo.fnIsGUID
(
@GUID CHAR(36)
)
RETURNS BIT
AS
BEGIN
RETURN (
SELECT CASE
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

)
END

E 12°55'05.63"
N 56°04'39.26"

GoDaddy
Yak Posting Veteran

64 Posts

Posted - 2009-06-23 : 15:03:52

yours works. I guess i'll use this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources