SQL内置函数Charindex用法
Author/cadenza7
--Example 1
DECLARE @StartPosition INT
DECLARE @EndPosition INT
DECLARE @str NVARCHAR(100)
DECLARE @StrLeng INT
SET @StartPosition=charindex('aa', 'dddddaaJackJonesbbbfffffff')
--SET @StartPosition=patindex('%aa%', 'dddddaaJackJonesbbbfffffff')
SELECT @StartPosition
SET @EndPosition=charindex('bbb', 'dddddaaJackJonesbbbfffffff')
SELECT @EndPosition
SET @StrLeng=@EndPosition-@StartPosition
SET @str=substring('dddddaaJackJonesbbbfffffff',@StartPosition+len('aa'),@EndPosition-@StartPosition-len('aa'))
SELECT @str
--Out: JackJones
--Example 2
DECLARE @Count INT
DECLARE @Range NVARCHAR(1000)
DECLARE @Position INT
DECLARE @Type NVARCHAR(10)
set @Type='C1'
set @Range=(select jyfw from dbo.DriverSchool where ssdq=53060000 and id='53060000000001')
select @Range as '经营范围'
SET @Position=charindex(@Type,@Range )
SELECT @Position
set @Range=substring(@Range,@Position,10)
SELECT @Range
SET @Position=charindex('辆',@Range)
SELECT @Position
set @Count=substring(@Range,len(@Type)+2,@Position-(len(@Type)+2))
select @Count
--Out: 130
--Example 3
--遍历数据并查找VarChar中的某一类型的数据进行累加
--使用非游标方式遍历数据库中的记录
select jyfw,* from dbo.DriverSchool where ssdq=53290000 order by id asc
DECLARE @Count INT
DECLARE @Range NVARCHAR(1000)
DECLARE @Position INT
DECLARE @Type NVARCHAR(10)
--声明循环用的“指针”
declare @min varchar(20)
--赋初值
select @min=min(id) from dbo.DriverSchool where ssdq=53290000
Set @Count=0
--开始循环
while @min is not null
begin
set @Type='B2' --A1、A2、A3、B1、B2、C1、C2、C3、C4、D、E等等
set @Range=(select jyfw from dbo.DriverSchool where ssdq=53290000 and id=@min)
--select @Range as '经营范围'
SET @Position=charindex(@Type,@Range )
--SELECT @Position
if @Position=0
begin
select @min=min(id) from dbo.DriverSchool where ssdq=53290000 and id>@min --更新“指针”内容,使之移到下一记录
continue
end
set @Range=substring(@Range,@Position,10)
--SELECT @Range
SET @Position=charindex('辆',@Range)
--SELECT @Position
if @Position=0
begin
select @min=min(id) from dbo.DriverSchool where ssdq=53290000 and id>@min
continue
end
set @Count=@Count+convert(int,substring(@Range,len(@Type)+2,@Position-(len(@Type)+2)))
--print @min --打印当前“指针”的值
select @min=min(id) from dbo.DriverSchool where ssdq=53290000 and id>@min
end
select @Type+':'+convert(varchar(10),@Count) as 'Count'
--Out: B2:173
至此实现方式已经完成,如需更强的扩展性可考虑封装成存储过程,并对where条件以及@type和@Position等参数进行传递,最后返回@Count就可以。
版权所有,转载请务必保留以下信息,否则将追究法律责任!!!