sql - 计算SQL Server中字符串字段中的字母字符数

我正在使用SQL Server Management Studio v17.9.1,我在数据库表中有包含字母数字字符串的列,我需要计算字符串中的字母,并生成一张表格,显示字母数目的字母数。

感谢你的帮助。

例如:Table1

 

ID


----------


A00001


AB0001


AC123


CB00AD


1234AD



聚合结果:

 

No of letters Count of records


----------- ----------- 


1 1


2 3


3 0


4 1



时间:

一个简单的方法是基于替换

 

SELECT LEN(


REPLACE(


REPLACE(


REPLACE( 


REPLACE(


REPLACE(


REPLACE( 


REPLACE(


REPLACE(


REPLACE(


REPLACE(myColumn, '0', ''),


 '1',''),


 '2',''),


 '3',''),


 '4',''),


 '5',''),


 '6',''),


 '7',''),


 '8',''),


 '9','')


) num_char


from your_table 



你可以从内联Table-Valued函数的性能中获益,以执行计数,如果您能理解函数的代码,则可以使用计数表生成空行(如果需要)。

 

CREATE FUNCTION dbo.CountChars(


 @String varchar(8000),


 @Pattern varchar(100)


)


RETURNS TABLE WITH SCHEMABINDING


AS


RETURN


WITH 


E(n) AS(


 SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)


),


E2(n) AS(


 SELECT a.n FROM E a, E b


),


E4(n) AS(


 SELECT a.n FROM E2 a, E2 b


),


cteTally(n) AS(


 SELECT TOP( LEN(@String)) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n


 FROM E4


)


SELECT COUNT(*) CharCount


FROM cteTally


WHERE SUBSTRING( @String, n, 1) LIKE @Pattern



GO



SELECT CharCount, COUNT(*)


FROM Table1


CROSS APPLY dbo.CountChars( ID, '[A-Za-Z]')


GROUP BY CharCount;



使用临时计数表的另一个选项

示例

 

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



 

Letters Cnt


1 1


2 3


4 1



...