x

remove empty strings from paramater list

I have a report stored procedure using a multi value input for paramaters

the input uses a comma split string as the paramater.

i need to remove any spaces entered by the user so that the paramater string will evaluate the records it needs to pull.

Below is the piece of code in my stored procedure that reads the users items

DECLARE @ITEMLIST VARCHAR(MAX)
SET  @ITEMLIST = (ltrim(rtrim(STUFF((SELECT ','+'('''+REPLACE(@ITEM, ',', ''',''')+''')'),1,1,''))))


;WITH  ITEM_LIST AS
 (SELECT 1 AS StartPos, (ltrim(rtrim(@ITEM))) AS ITEMS,(CHARINDEX(',', @ITEM + ',')) AS EndPos
  UNION ALL
  SELECT EndPos + 1 AS StartPos , NULL, CHARINDEX(',' ,@ITEM + ',' , EndPos + 1) AS EndPos 
  FROM ITEM_LIST
  WHERE CHARINDEX(',', @ITEM + ',', EndPos + 1) <> 0)

now if the end user places a value of 12345,98765 - it works because it has no space between the comma and the second item. But 12345, 98765 does not work. The report renders but does not evaluate the second item

Any assistance would be greatly appreciated.
more ▼

asked Sep 27, 2011 at 04:10 PM in Default

siera_gld gravatar image

siera_gld
1k 78 84 85

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Maybe I am missing something, but could you not simply search the string before it is passed into your logic?

i.e: REPLACE(@ITEM,SPACE(1),'')
more ▼

answered Sep 28, 2011 at 05:29 AM

simonrichards74 gravatar image

simonrichards74
47 1

Thank you very much
Sep 28, 2011 at 08:25 AM siera_gld
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x5
x1

asked: Sep 27, 2011 at 04:10 PM

Seen: 562 times

Last Updated: Sep 27, 2011 at 04:10 PM