SplitList function not working with apostrophe's

[Mack2]

I am using a split list function to handle sending multiple values in a parameter. When I send it a value with an apostraphe, it does not work. I have attached the function below.

Thanks!!!

/****** Object: UserDefinedFunction [SplitList] Script Date: 10/12/2009 13:46:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Function [SplitList]
(
@CommaDelimitedFieldNames Varchar(8000),
@CharToFind VarChar(10)
)
Returns @Tbl_FieldNames Table (Position Integer Identity(1,1), FieldName VarChar(8000)) As
Begin
Set @CommaDelimitedFieldNames = @CommaDelimitedFieldNames + @CharToFind
Declare @Pos1 Int
Declare @pos2 Int

Set @Pos1=1
Set @Pos2=1
While @Pos1<DataLength(@CommaDelimitedFieldNames)
Begin
Set @Pos1 = CharIndex(@CharToFind, @CommaDelimitedFieldNames,@Pos1)
Insert @Tbl_FieldNames Select Cast(Substring(@CommaDelimitedFieldNames,@Pos2,@Pos1-@Pos2) As VarChar(8000))
Set @Pos2=@Pos1+1
Set @Pos1 = @Pos1+1
End
Return
End

 

[gmmastros]

It does work. I just tested it.

The problem is probably the way you are calling the function. Can you show us?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

For splitting functions check also this thread

http://forum.lessthandot.com/viewtopic.php?f=17&t=7566

 

[JeffModen]

Be careful which one(s) you chose from that thread... the ones with recursive CTE's are kind of slow and the ones that use the "numbers" table do a lot of extra and unnecessary work. For example, one of them has a conversion of LEN to INT... totally not necessary.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[Emtucifor]

Mack2: The plural of apostrophe is apostrophes. Not apostrophe's. I just had to say that.

Jeff: You haven't answered my question in that thread, where I asked if your comments (on int and CTEs being slow) were about my (recursive) CTE. Because I think you're in danger of having misspoken, I just need to know what you were actually saying before I call you on it.

 

[JeffModen]

Sorry... I'll take a look.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[Mack2]

Thanks Everyone. Turns out I had to change how I was passing the parameter from reporting services. The function is just fine.

 

[JeffModen]

Jeff: You haven't answered my question in that thread, where I asked if your comments (on int and CTEs being slow) were about my (recursive) CTE. Because I think you're in danger of having misspoken, I just need to know what you were actually saying before I call you on it.

Nope... wasn't misspoken... it wasn't your code I was talking about. Heh... be careful what you call me on, Erik... I might not actually be talking about you. ;-)

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[Emtucifor]

And didn't I do just that (be careful)? I didn't call you on anything, just told you I was wondering if I needed to!