Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | Remove Duplicate Word(s) from the String/Sentence

Write a T-SQL function that removes the duplicate word(s) from the String/Sentence. Please check out the sample input and the expected output.

Sample Input

Id InputString
1 A very A long stringx, d D testing the string
2 A very A long string, d D testing the string
4 A very A long string. d D testing the string
5 Pawan Pawan

Expected Output

Id InputString OutputString
1 A very A long stringx, d D testing the string A very long stringx, d testing the string
2 A very A long string, d D testing the string A very long string, d testing the
4 A very A long string. d D testing the string A very long string. d testing the
5 Pawan Pawan Pawan

Rules/Restrictions

  • The challenge is to do it with T-SQL statements only.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table and insert some sample data


--

CREATE TABLE RemoveDuplicateWords
(	
	 Id INT
	,InputString VARCHAR(1000)                                                           
)
GO
	
INSERT INTO RemoveDuplicateWords VALUES
(1,'A very A long stringx, d D testing the string'  ),
(2,'A very A long string, d D testing the string'   ),
(4,'A very A long string. d D testing the string'   ),
(5,'Pawan Pawan'                                    )
GO

SELECT * FROM RemoveDuplicateWords
GO


--

SOLUTION – 1

--

CREATE FUNCTION RemoveDupicateFromString
(
@Inputs VARCHAR(1000)
,@Ignore VARCHAR(100) --Pipe Separated
)
RETURNS VARCHAR(1000)
AS
BEGIN

DECLARE @ProductTotals TABLE
(
ProductID int,
Revenue money
)
DECLARE @V AS VARCHAR(1000)
SET @V = ''
DECLARE @IgnoreChrs VARCHAR(MAX)

SET @IgnoreChrs = @Ignore
DECLARE @finalString AS VARCHAR(1000)

SET @finalString = ''
DECLARE @InputString AS VARCHAR(MAX)

SET @InputString = RTRIM(LTRIM(@Inputs))

DECLARE @Pawan TABLE ( Value VARCHAR(1) )
DECLARE @TempBills TABLE (strs VARCHAR(1000))

DECLARE @x1 AS XML
SET @x1 = CAST('<a>'+ REPLACE(@IgnoreChrs,'|','</a><a>')+ '</a>' AS XML)

INSERT INTO @Pawan
SELECT t.value('.', 'VARCHAR(1)') Value FROM @x1.nodes('/A') AS x(t)

INSERT INTO @TempBills
SELECT @InputString strs

;WITH CTE1 AS
(
SELECT SplitedValue , ROW_NUMBER() OVER (ORDER BY (SELECT 1)) rnk FROM
(
SELECT CAST((''+replace(@InputString,' ' ,'')+'') as xml) xmlcol FROM @TempBills e
) s
OUTER APPLY
(
SELECT ProjectData.D.value('.', 'VARCHAR(MAX)') as SplitedValue
FROM s.xmlcol.nodes('X') as ProjectData(D)
)r
)
SELECT @finalString = STUFF((SELECT ' ' + SplitedValue
FROM
(
SELECT SplitedValue , rnk FROM
(
SELECT * , ROW_NUMBER() OVER (PARTITION BY col ORDER BY rnk) duprnk FROM
(
SELECT * , RIGHT(SplitedValue,1) r , CASE WHEN Value IS NOT NULL THEN SUBSTRING(SplitedValue,0,LEN(SplitedValue)) ELSE SplitedValue END col FROM CTE1
LEFT JOIN @Pawan p ON p.Value = RIGHT(SplitedValue,1)
)p
)m WHERE m.duprnk = 1
)y ORDER BY rnk FOR XML PATH('')),1,1,'')

RETURN @finalString

END

--

Usage


--

SELECT Id,InputString,dbo.RemoveDupicateFromString(InputString,',|;|.') OutputString FROM RemoveDuplicateWords
GO

--

Add a comment if you have a solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

Advertisements