T-SQL Remove non-unique words from a string

naisnet
naisnet used Ask the Experts™
on
I have a web search function that takes a first and last name, assembles it into a single string and then searches it against a full text catalog of names.  The issue I'm having is that I need to remove duplicates from the string in a t-sql function before searching:

INPUT:  Jean VonJean, Jean Ann

DESIRED RESULT: VonJean, Jean Ann

It's a rare circumstance but reaking havoc with the app.  :-)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2011
Commented:
function to remove duplicates from a comma delimited string.

CREATE FUNCTION dbo.fnDistinctList
(
    @List VARCHAR(MAX)
)
    RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @Delim CHAR  = ','
    DECLARE @ParsedList TABLE
    (
    Item VARCHAR(MAX)
    )
    DECLARE @list1 VARCHAR(MAX), @Pos INT, @rList VARCHAR(MAX)
    SET @list = LTRIM(RTRIM(@list)) + @Delim
    SET @pos = CHARINDEX(@delim, @list, 1)
    WHILE @pos > 0
    BEGIN
    SET @list1 = LTRIM(RTRIM(LEFT(@list, @pos - 1)))
    IF @list1 <> ''
    INSERT INTO @ParsedList VALUES (CAST(@list1 AS VARCHAR(MAX)))
    SET @list = SUBSTRING(@list, @pos+1, LEN(@list))
    SET @pos = CHARINDEX(@delim, @list, 1)
    END
    SELECT @rlist = COALESCE(@rlist+',','') + item
    FROM (SELECT DISTINCT Item FROM @ParsedList) t
    RETURN @rlist
END

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial