找出字段中某字符的个数

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

-- Author : htl258(Tony)

-- Date   : 2009-04-17 11:59:36

-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)

--          Jul  9 2008 14:43:34

--          Copyright (c) 1988-2008 Microsoft Corporation

--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)

-- Blog   : http://blog.csdn.net/htl258

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

--> 生成测试数据表: [tb]

IF OBJECT_ID('[tb]') IS NOT NULL

    DROP TABLE [tb]

GO

CREATE TABLE [tb]

(

    ID INT,

    Col VARCHAR(10)

)

INSERT INTO [tb]

SELECT 1, 'ABCDABCD' UNION ALL

SELECT 2, 'ADEADE' UNION ALL

SELECT 3, 'BCDEFG'

 

--查询表中字段col中字符A的个数

 

SELECT *,num = LEN(Col)-LEN(REPLACE(Col,'A',''))

FROM tb

/*

ID          Col        num

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

1           ABCDABCD   2

2           ADEADE     2

3           BCDEFG     0

 

(3 行受影响)

*/