Count columns that ends with the same value starting with a value you choose

Question

I have a table which is like:

Identfier
Abc XY: a(123)
Abcd XY: a(123)
Abcde XY: b(123)
dfe XY: b(123)
dfeg XY: b(123)
dfegh XY: c(123)
ijk XY: c(123)

I want to count columns that they end with the same value starting from a certain value I choose It doesn't matter how the columns start For example if I chose "XY" in our example ,I have an output like this:

Identifier last part	number of repetition
XY: a(123)	2
XY: b(123)	3

Thank you.

Answer 1

Try This...

Create Table tblText1(data  nvarchar(Max))

Insert into tblText1(data) values('Abc XY: a(123)')
Insert into tblText1(data) values('Abcd XY: a(123)')
Insert into tblText1(data) values('Abcde XY: b(123)')
Insert into tblText1(data) values('dfe XY: b(123)')
Insert into tblText1(data) values('dfeg XY: b(123)')
Insert into tblText1(data) values('dfegh XY: c(123)')
Insert into tblText1(data) values('ijk XY: c(123)')

Select distinct Right(data,Len(data)-charindex('XY:',data)+1) as [Identifier last part],Count(data) over (partition By Right(data,Len(data)-charindex('XY:',data)+1) ) as [number of repetition] from tblText1

In this example first, you need to calculate the end part of the column and then add a partition to it.