Tags

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


SQL Puzzle | Names with NON repeating characters Puzzle | SQL Interview Question

In this puzzle we have to write a sql server query to display the names which does not have any repeating letters.

Please check the sample input and the expected output.

Sample Input

ID Nm
1 Pawan
2 Ramesh
3 Shiv
4 Isha
5 Naman
6 Avtaar

Expected Output

Id Nm
2 Ramesh
3 Shiv
4 Isha

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

--

CREATE TABLE Names
(
	 ID INT
	,Nm VARCHAR(20)
)
GO

INSERT INTO Names VALUES
(1,'Pawan'),
(2,'Ramesh'),
(3,'Shiv'),
(4,'Isha'),
(5,'Naman'),
(6,'Avtaar')
GO

SELECT * FROM Names
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 | Usign MASTER..SPT_VALUES

--


SELECT DISTINCT Id,Nm
FROM
(
	SELECT Id,Nm,Charx,MAX(COUNT(*)) OVER(PARTITION BY Id,Nm) u FROM Names N
	CROSS APPLY
	(
		SELECT SUBSTRING(Nm,number,1) Charx
		FROM 
		( 
			SELECT DISTINCT number FROM
			MASTER..SPT_VALUES WHERE number > 0 AND number <= DATALENGTH(N.Nm)
		)v	
	)t
	GROUP BY Id,Nm,Charx
)z WHERE u = 1

--

Output-1

--                            


Id          Nm
----------- --------------------
2           Ramesh
3           Shiv
4           Isha

(3 rows affected)




--

SOLUTION – 2 | Using Numbers Table

--


;WITH SingleDigits(Number) AS
(
    SELECT Number
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
    (9), (0)) AS X(Number)
)
,Series AS
(
    SELECT (d1.Number+1) + (10*d2.Number) + (100*d3.Number) + (1000*d4.Number) Number
    from
    SingleDigits as d1,
    SingleDigits as d2,
    SingleDigits as d3,
    SingleDigits as d4
)
SELECT DISTINCT Id,Nm
FROM
(
	SELECT Id,Nm,Charx,MAX(COUNT(*)) OVER(PARTITION BY Id,Nm) u FROM Names N
	CROSS APPLY
	(
		SELECT SUBSTRING(Nm,number,1) Charx
		FROM 
		( 
			SELECT DISTINCT number FROM
			Series WHERE number > 0 AND number <= DATALENGTH(N.Nm)
		)v	
	)t
	GROUP BY Id,Nm,Charx
)z WHERE u = 1 

--

Output-2

--                            

Id          Nm
----------- --------------------
2           Ramesh
3           Shiv
4           Isha

(3 rows affected)

--

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

Related Puzzle

--                            

https://msbiskills.com/2017/08/11/sql-puzzle-the-distinct-records-multi-col-puzzle/

--

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com