SplitFunction but with Twist :)

Question

Post reply

SplitFunction but with Twist :)

jbon007

SSChasing Mays

Points: 602
More actions
December 10, 2010 at 9:10 am

#232446

Dear All,
I have field called Consultant. The data in the field is:
Consultant
Bloggs, Joe
Mouse, Mickey
Rabbit, Roger
i have been using this split function:
USE [ColossusUser]
GO
/****** Object: UserDefinedFunction [NLHNET\monkey].[SplitStringList] Script Date: 12/10/2010 14:48:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*SQL Split Function to change drop down list value into comma Delimitted*/
ALTER Function [NLHNET\monkey].[SplitStringList](@ListString nvarchar(MAX), @delim varchar(2) = ',')
Returns @vals table (Item nvarchar(MAX))
As
Begin
Declare @CurLoc int, @Item nvarchar(MAX)
While len(@ListString) > 0
Begin
Set @CurLoc = charindex(@delim, @ListString, 1)
If @CurLoc = 0
Begin
Set @Item = @ListString
Set @Item = Ltrim(RTrim(@Item))
Set @ListString = ''
End
Else
Begin
Set @Item = left(@ListString, @CurLoc - 1)
Set @Item = Ltrim(RTrim(@Item))
Set @ListString = Substring(@ListString, @CurLoc + 1, Len(@ListString) - @CurLoc)
End
Insert into @vals (Item) values (@Item)
End
Return
End
since the data has already comma's how do i deal with this. I'm not getting the desired result in reporting services for my stored procedure when use this in the sql code
AND Consultant IN (Select [Item] From SplitStringListCons(@Consultant, ','))
i am populating a multivalue pick list in reporting services due to the comma being already in the consultant names its causing me slight headache.
many thanks
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

IMO you should split your @consultants string using another split character !!

Your data contains a comma, and your current split function is called using a comma as split character, so it will not generate the data you expect !

"Do, John" <>"Do"

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

mister.magoo SSC-Forever Points: 47174 More actions · Answer 2

Do you have a consultant id that you an use instead as the data value in your drop down?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

How about some feedback, so others can your this thread as a ref.;-)