I am using SQL Server Management Studio v17.9.1. I have column in a database table that contains alphanumeric strings. I need to count the letters in the string and produce a table of the aggregate results showing the number of letters by a count of the records which had that number of letters in the column.
I am also trying, if possible, to make the list dynamic so the rows in the aggregated results goes as far as the max number of letters in a string in the table column.
The examples I have found so far look to scan the string character by character which will be very performance intensive and I have also looked at the replace function but it does not look like it works with regex and I would have to replace each letter in the alphabet in turn.
Thanks for any help.
For example: Table1
ID ---------- A00001 AB0001 AC123 CB00AD 1234AD
Aggregated results:
No of letters Count of records ----------- ----------- 1 1 2 3 3 0 4 1
Advertisement
Answer
Just another option using an ad-hoc tally table
Example
Declare @YourTable Table ([ID] varchar(50)) Insert Into @YourTable Values ('A00001') ,('AB0001') ,('AC123') ,('CB00AD') ,('1234AD') Select Letters ,Cnt = count(*) From ( Select Letters = count(*) From @YourTable A Join ( Select Top (select max(len(ID)) from @YourTable) N=Row_Number() Over (Order By (Select NULL)) From master..spt_values n1 ) B on n<=len(ID) Where substring(ID,N,1) not like '[0-9]' Group By ID ) A Group By Letters
Returns
Letters Cnt 1 1 2 3 4 1