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:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE tbl_Strings (name VARCHAR(30)) GO INSERT INTO tbl_Strings VALUES ('India'),('Southern Europe') ,('Japan'),('Indonesia') ,('Swaziland'),('South Africa') ,('United Arab Emirates'),('United States') GO |
Solution:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
;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:
|
1 2 3 4 5 6 7 8 9 10 11 |
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 |
