CODEWITHSUNDEEP
sqlsql-servert-sql
priyanka.sarkar
priyanka.sarkar

Reputation: 26498

Char count in sql server

Input: AA1

The output will be:

Char  Count
A     2
1     1

Upvotes: 0

Views: 492

Answers (1)

gbn
gbn

Reputation: 432180

This is easier because it's simple count, rather that consective counts in your other question.

DECLARE @Numbers TABLE (Num smallint NOT NULL PRIMARY KEY)

INSERT @Numbers (Num)
SELECT TOP 12
    ROW_NUMBER() OVER (ORDER BY c1.NAME)
FROM
    sys.columns c1

DECLARE @STuff TABLE (Seq char(12) NOT NULL PRIMARY KEY)

INSERT @STuff (Seq) VALUES ('12AABCCDDDDE')
INSERT @STuff (Seq) VALUES ('333345RRRR11')

SELECT
   Seq, Single, COUNT(*)
FROM
   @Numbers N
   CROSS APPLY 
   (SELECT Seq, SUBSTRING(Seq, Num, 1) AS Single FROM @Stuff) S
GROUP BY
   Seq, Single
HAVING
    COUNT(*) > 1
ORDER BY
    Seq, Single

Upvotes: 2

Related Questions