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  Analysis Server and Reporting Services (2005)  Apostrophe in multi-value parameter array

Author  Topic  kestrel Starting Member 1 Post Posted - 2010-03-25 : 14:12:24 I have a function that splits a multi-value parameter, inserts a comma delimiter, and passes to a stored procedure to retrieve user information based on a field that matches the values in the array. The possibility exists where the last name may have an apostrophe, resulting in that record not matching, as each array value is formatted in single quotes, so a user with the last name O'Brien, after the reformatting, would appear as 'O'Brien', resulting in only the 'O' part looking for a match, so that record is not returned. Any thoughts on how to format the function code to replace any value that has an apostrophe for the retrieval, then back to original state for output? Here is the function, which I pulled off the web...works great except for the one record with the apostrophe. The *** line after 017 is where I'm thinking I can somehow escape the apostrophe, but don't know how..tried double apostrophe, but as they are in single quotes, the quotes are closed at the wrong position...any help? 001: set ANSI_NULLS ON 002: set QUOTED_IDENTIFIER ON 003: go 004: ALTER FUNCTION [dbo].[fnSplit] 005: /* This function is used to split up multi-value parameters */ 006: ( 007: @ItemList NVARCHAR (max), 008: @delimiter CHAR(1) 009: ) 010: RETURNS @IDTable TABLE (Item NVARCHAR(255) collate database_default ) 011: AS 012: BEGIN 013: DECLARE @tempItemList NVARCHAR(max) 014: SET @tempItemList = @ItemList 015: DECLARE @i INT 016: DECLARE @Item NVARCHAR(max) 017: SET @tempItemList = REPLACE (@tempItemList, @delimiter + ' ', @delimiter) *** SET @tempItemList = REPLACE (@tempItemList, ??? , ???) 018: SET @i = CHARINDEX(@delimiter, @tempItemList) 019: WHILE (LEN(@tempItemList) > 0) 020: BEGIN 021: IF @i = 0 022: SET @Item = @tempItemList 023: ELSE 024: SET @Item = LEFT(@tempItemList, @i - 1) 025: INSERT INTO @IDTable(Item) VALUES(@Item) 026: IF @i = 0 027: SET @tempItemList = '' 028: ELSE 029: SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i) 030: SET @i = CHARINDEX(@delimiter, @tempItemList) 031: END 032: RETURN 033: END