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

ID

----------

A00001

AB0001

AC123

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

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')

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