Tags

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


SQL Puzzle | Remove Words from a string in a table

In this puzzle the requirement is you have to remove words from a string which are of length 7. The data is present in a column of a table.
Please check out the sample input values and sample expected output below.

Sample Input

Id Vals
1 Hello Supercalifragilisticexpialidocious 01234567890 World 123 @#$!@# pneumonoultramicroscopicsilicovolcanoconiosis
2 Hello Supercalifragilisticexpialidocious 01234567890 World 123 @#$!@# Mayank

Expected Output

Id Vals
1 Hello World 123 @#$!@#
2 Hello World 123 @#$!@# Mayank

Script

Use below script to create table and insert sample data into it.

--


CREATE TABLE RemoveWordsFromString
(
	 Id INT
	,Vals NVARCHAR(MAX)
)
GO

INSERT INTO RemoveWordsFromString VALUES
(1,'Hello Supercalifragilisticexpialidocious 01234567890 World 123 @#$!@# pneumonoultramicroscopicsilicovolcanoconiosis'),
(2,'Hello Supercalifragilisticexpialidocious 01234567890 World 123 @#$!@# Mayank')
GO


--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution 1


--
 
;WITH CTE AS
(
	SELECT * FROM
	(
		SELECT Id, CAST('<A>'+ REPLACE(Vals,' ','</A><A>')+ '</A>' AS XML) Vals FROM RemoveWordsFromString 
	)r
	CROSS APPLY
	(
		SELECT t.value('.', 'NVARCHAR(MAX)') Value 
		FROM r.Vals.nodes('/A') AS x(t)
	)u
	WHERE LEN(Value) <= 7
)	
SELECT DISTINCT c.Id,STUFF 
                ((
                SELECT ' ' + CAST(a.Value AS VARCHAR(MAX))
                FROM CTE a
                WHERE ( a.Id = c.Id )
                FOR XML PATH('')
                ) ,1,1,'') 
                AS Vals
FROM CTE c

--

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

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com