Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

How to get a string with unique words in SQL Server

Author: Alanna Kremer

This article will demonstrate how we can get a string with unique words in SQL Server.

In one of my previous articles, I have explained about User Defined Functions (UDF) and how we can create a user defined function in SQL Server. In this article I will create a Scalar-valued Function which will get a string of duplicate words as input and return a string with unique or distinct words as output. I have used another parameter of delimiter which is used to separate the words and then rejoin the words to form the string. This parameter can be omitted if you want to use this function with a single delimiter otherwise you can call this function with two parameters. You can provide delimiter (space, comma etc) in the function where needed.

Here is the function to get a string with unique words.

CREATE FUNCTION [dbo].[GetStringWithUniqueWords]
(
    @String            VARCHAR(MAX),
    @Delimiter        CHAR
)
RETURNS
VARCHAR(MAX)
AS

BEGIN
    
    DECLARE @WordsList TABLE
    (
        Word        VARCHAR(MAX)
    )

    DECLARE @Word1            VARCHAR(MAX)
    DECLARE @Position          INT
    DECLARE @ReturnString    VARCHAR(MAX)

    SET @String = LTRIM(RTRIM(@String)) + @Delimiter
    SET @Position = CHARINDEX(@Delimiter, @String, 1)

    WHILE @Position > 0
        BEGIN
           
            SET @Word1 = LTRIM(RTRIM(LEFT(@String, @Position - 1)))
           
            IF @Word1 <> ''
                BEGIN

                    IF NOT EXISTS(SELECT Word FROM @WordsList WHERE Word = @Word1)
                        BEGIN
                            INSERT INTO @WordsList
                                (Word)
                            VALUES (CAST(@Word1 AS VARCHAR(MAX)))
                        END

                    SET @String = SUBSTRING(@String, @Position + 1, LEN(@String))
                    SET @Position = CHARINDEX(@Delimiter, @String, 1)
               
                END
        END

    SELECT    @ReturnString = ISNULL(@ReturnString + @Delimiter,'') + Word
    FROM    
                    (SELECT Word FROM @WordsList) T

    RETURN @ReturnString

END

Now call the function and see the result

SELECT dbo.GetStringWithUniqueWords('This is is a test test string string',' ') AS StringWithUniqueWords

Result: This is a test string

 

 

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< How to create a Trigger to pass parameter to stored procedure in SQL Server

Disclaimer - Privacy
© 2002-2017 DevASP.net