SQL PUZZLE | Find string with all same characters? | SQL Interview Question

Most of my friends are asking this question in interviews so I thought lets share with you guys. In this puzzle you have to find string with multiple characters. The length of the string should be greater than 1 and all the characters should be same. For more details please refer the sample input and expected output.

Sample Input

Expected Output

Script to create table and insert sample date

--

CREATE TABLE FindSameCharacters
(
	 Id INT
	,Vals VARCHAR(10)
)
GO

INSERT INTO FindSameCharacters VALUES
(1,'aa'),
(2,'cccc'),
(3,'abc'),
(4,'aabc'),
(5,NULL),
(6,'a'),
(7,'zzz'),
(8,'abc')
GO

SELECT * 
FROM FindSameCharacters
GO

--

SOLUTION 1

--


SELECT * 
FROM FindSameCharacters
WHERE LEN(Vals) > 1 AND LEN(REPLACE(Vals,SUBSTRING(Vals,1,1),'')) = 0
GO

--

OUTPUT

--


Id          Vals
----------- ----------
1           aa
2           cccc
7           zzz

(3 rows affected)

--

SOLUTION 2

--


SELECT *
FROM FindSameCharacters
WHERE Vals = REPLICATE(SUBSTRING(Vals,1,1),LEN(Vals)) AND LEN(Vals) > 1
Go

--

OUTPUT

--


Id          Vals
----------- ----------
1           aa
2           cccc
7           zzz

(3 rows affected)

--

SOLUTION 3 by Swepeso

--


SELECT * 
FROM FindSameCharacters
WHERE LEN(Vals) > 1 
AND Vals NOT LIKE '%' + QUOTENAME('^' + SUBSTRING(Vals, 1, 1)) + '%';
GO

--

OUTPUT

--


Id          Vals
----------- ----------
1           aa
2           cccc
7           zzz

(3 rows affected)

--

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

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.

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