passing \ as a parameter to a function

I have a UDF for splitting delimiter strings:
CREATE FUNCTION Split
(@.Source varchar (5000)
,@.Delimiter varchar (10) = ','
)
RETURNS @.T table (F1 varchar (100))
AS
--Accepts a source string @.Source and parses it to break it up into single
units
--delineated by @.Delimiter.
--Returns a Single Column Table with each row containing one of the split
chunks
--e.g. @.Source = 'SP,AQ,YD'
-- Returns @.T with three rows:
-- SP
-- AQ
-- YD
-- or @.Source = 'P1=V1, P2=V2'
-- Returns @.T with two rows:
-- P1=V1
-- P2=V2
BEGIN
DECLARE @.w varchar (5000)
DECLARE @.inte int
SET @.W = @.Source + @.Delimiter
WHILE len(@.W) > 0
BEGIN
SET @.inte = patindex('%,%',@.w) - 1
INSERT @.T (F1) VALUES (substring(@.W, 1, @.inte))
SET @.W = substring(@.W,@.inte+2,len(@.W)-(@.inte+1))
END
RETURN
END
A typical use of this would be:
DECLARE @.Reps table (RepIn varchar (20))
INSERT @.Reps (RepIn) SELECT * FROM Split(@.RepSelect,',')
Assuming that a parameter @.RepSelect is passed, containing 'Fred,Joe,Andy,
the @.Reps table would have three records with one of the names in each.
e.g. Fred
Joe
Andy
It can also be called "inline":
SELECT s.* FROM tblSales s
INNER JOIN (SELECT * FROM split(@.Reps,',') r
ON s.Rep = r.F1
This all works fine until I try to call it using '\' as the delimiter
parameter, then I just get an error that says "Invalid length parameter
passed to the substring function"
Here is sample code to run this:
DECLARE @.NewPath varchar (100)
--Use this pair and it works
-- SET @.NewPath = 'c:,MSSQL,Data,MSSQL,DBFile.mdf'
-- SELECT * FROM split(@.NewPath, ',')
--Use this pair and it fails
SET @.NewPath = 'c:\MSSQL\Data\MSSQL\DBFile.mdf'
SELECT * FROM split(@.NewPath, '\')
Sorry to be so long winded, but does anyone have any ideas?
Regards,
-Rob
--
Robert Marmion
ITBridges Inc
609 844 0949
"Connecting your Business with your Software"Hi
I have modified a little bit the function written by Dejan Sarka.
IF OBJECT_ID('dbo.TsqlSplit') IS NOT NULL
DROP FUNCTION dbo.TsqlSplit
GO
CREATE FUNCTION dbo.TsqlSplit
(@.List As varchar(8000),@.delim VARCHAR(2))
RETURNS @.Items table (Item varchar(8000) Not Null)
AS
BEGIN
DECLARE @.Item As varchar(8000), @.Pos As int
WHILE DATALENGTH(@.List)>0
BEGIN
SET @.Pos=CHARINDEX(@.delim,@.List)
IF @.Pos=0 SET @.Pos=DATALENGTH(@.List)+1
SET @.Item = LTRIM(RTRIM(LEFT(@.List,@.Pos-1)))
IF @.Item<>'' INSERT INTO @.Items SELECT @.Item
SET @.List=SUBSTRING(@.List,@.Pos+DATALENGTH(@.d
elim),8000)
END
RETURN
END
GO
--A typical use of this would be:
DECLARE @.Reps table (RepIn varchar (20))
declare @.RepSelect varchar(50)
set @.RepSelect='Fred\Joe\Andy'
INSERT @.Reps (RepIn) SELECT * FROM TsqlSplit(@.RepSelect,'')
select * from @.Reps
"RMarmion" <RMarmion@.Discussions.Microsoft.com> wrote in message
news:76AF1578-99B3-45FE-A24E-4D82B5435CF8@.microsoft.com...
>I have a UDF for splitting delimiter strings:
> CREATE FUNCTION Split
> (@.Source varchar (5000)
> ,@.Delimiter varchar (10) = ','
> )
> RETURNS @.T table (F1 varchar (100))
> AS
> --Accepts a source string @.Source and parses it to break it up into
> single
> units
> --delineated by @.Delimiter.
> --Returns a Single Column Table with each row containing one of the split
> chunks
> --e.g. @.Source = 'SP,AQ,YD'
> -- Returns @.T with three rows:
> -- SP
> -- AQ
> -- YD
> -- or @.Source = 'P1=V1, P2=V2'
> -- Returns @.T with two rows:
> -- P1=V1
> -- P2=V2
> BEGIN
> DECLARE @.w varchar (5000)
> DECLARE @.inte int
> SET @.W = @.Source + @.Delimiter
> WHILE len(@.W) > 0
> BEGIN
> SET @.inte = patindex('%,%',@.w) - 1
> INSERT @.T (F1) VALUES (substring(@.W, 1, @.inte))
> SET @.W = substring(@.W,@.inte+2,len(@.W)-(@.inte+1))
> END
> RETURN
> END
> A typical use of this would be:
> DECLARE @.Reps table (RepIn varchar (20))
> INSERT @.Reps (RepIn) SELECT * FROM Split(@.RepSelect,',')
> Assuming that a parameter @.RepSelect is passed, containing 'Fred,Joe,Andy,
> the @.Reps table would have three records with one of the names in each.
> e.g. Fred
> Joe
> Andy
> It can also be called "inline":
> SELECT s.* FROM tblSales s
> INNER JOIN (SELECT * FROM split(@.Reps,',') r
> ON s.Rep = r.F1
> This all works fine until I try to call it using '' as the delimiter
> parameter, then I just get an error that says "Invalid length parameter
> passed to the substring function"
> Here is sample code to run this:
> DECLARE @.NewPath varchar (100)
> --Use this pair and it works
> -- SET @.NewPath = 'c:,MSSQL,Data,MSSQL,DBFile.mdf'
> -- SELECT * FROM split(@.NewPath, ',')
> --Use this pair and it fails
> SET @.NewPath = 'c:\MSSQL\Data\MSSQL\DBFile.mdf'
> SELECT * FROM split(@.NewPath, '')
> Sorry to be so long winded, but does anyone have any ideas?
> Regards,
> -Rob
> --
> Robert Marmion
> ITBridges Inc
> 609 844 0949
> "Connecting your Business with your Software"|||Here lies the pain:
> SET @.inte = patindex('%,%',@.w) - 1
You're still looking for the comma. And, BTW, you could just as well use
CHARINDEX.
ML|||Duh!
Thank you both so much. What a stupid error!!
-Rob
--
Robert Marmion
ITBridges Inc
609 844 0949
"Connecting your Business with your Software"
"ML" wrote:

> Here lies the pain:
> You're still looking for the comma. And, BTW, you could just as well use
> CHARINDEX.
>
> ML