给定一个较短字符串shortStr='ab',和一个较长字符串longStr='adkdabkwelabwkereabrsdweo2342ablk234lksdfsdf1abe';
判断shortStr在longStr中出现的次数的方案,要求使用sql实现:
方案一:使用replace函数:
declare @llongStr nvarchar(64) declare @shortStr nvarchar(16); set @llongStr='adkdabkwelabwkereabrsdweo2342ablk234lksdfsdf1abe'; set @shortStr='ab'; declare @newStr nvarchar(64); set @newStr=replace(@llongStr,@shortStr,''); declare @shortDisplayCount int; set @shortDisplayCount=(len(@llongStr)-len(@newStr))/len(@shortStr); select @shortDisplayCount;
打印结果:
方案二:使用charindex和substring实现:
declare @longStr nvarchar(64) declare @shortStr nvarchar(16); set @longStr='adkdabkwelabwkereabrsdweo2342ablk234lksdfsdf1abe'; set @shortStr='ab'; declare @findCount int; declare @idx int; set @findCount=0; while charindex(@shortstr,@longStr)>0 begin set @findCount+=1; set @idx=charindex(@shortstr,@longStr) set @longStr=substring(@longStr,@idx+len(@shortStr),len(@longStr)-@idx); print @longStr end print @findCount
输出结果:
kwelabwkereabrsdweo2342ablk234lksdfsdf1abe wkereabrsdweo2342ablk234lksdfsdf1abe rsdweo2342ablk234lksdfsdf1abe lk234lksdfsdf1abe e 5