SQL Server Interview: Advance SQL Query - Find a count of repeated character in a String

SQL Server Interview: Advance SQL Query – Find a count of repeated character in a String

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In this post, I am sharing one complex SQL Query interview question and solution for T-SQL Developers.

Last week, I asked this question to one of the T-SQL Developer.
If you are going for database developer interview, you must find and practice complex or advance SQL Queries.

Question: Find a count of given repeated character from a string

Create a table with sample data:

CREATE TABLE tbl_Strings (name VARCHAR(30))

INSERT INTO tbl_Strings

VALUES

('India'),('Southern Europe')

,('Japan'),('Indonesia')

,('Swaziland'),('South Africa')

,('United Arab Emirates'),('United States')

Solution:

;WITH CTE AS

(

SELECT name, CONVERT(VARCHAR(1),LEFT(name,1)) AS Letter, RIGHT(name, LEN(name)-1) AS Remain

FROM tbl_Strings

WHERE LEN(name)>1

UNION ALL

SELECT name, CONVERT(VARCHAR(1),LEFT(Remain,1)) AS Letter,

RIGHT(Remain, LEN(Remain)-1) AS Remain

FROM CTE

WHERE LEN(Remain)>0

)

SELECT

name

,Letter

,ASCII(Letter) AS CharAsciiCode

,COUNT(Letter) AS CountOfLetter

FROM CTE

WHERE Letter IN('a','A')

GROUP BY name, Letter, ASCII(Letter)

The Result:

name Letter CharAsciiCode CountOfLetter

------------------------------ ------ ------------- -------------

South Africa A 65 1

United Arab Emirates A 65 1

India a 97 1

Indonesia a 97 1

Japan a 97 2

South Africa a 97 1

Swaziland a 97 2

United Arab Emirates a 97 2

United States a 97 1

Jun 1, 2017Anvesh Patel

Comments: 4

Hemanth Sharma

November 25, 2017 at 11:58 am

In the above query why we need to use “ASCII(Letter) AS CharAsciiCode” in the select statement?
I tried without using that and got the output with no capital letters

For Eg: India
& output is like —– n 1 , d 1 , i 1 , a 1

Reply Cancel