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 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: