نویسنده : نویسندگان آزاد

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

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)

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