比如:
1 test123test124
2 test134
3 best
4 hitest
5 quicktestandbesttest我想用select查询这个text字段中,包含多少个test,并形成新的列,应该怎么做?谢谢.
SELECT [ID],[TEXT] FROM TBL
1 test123test124
2 test134
3 best
4 hitest
5 quicktestandbesttest我想用select查询这个text字段中,包含多少个test,并形成新的列,应该怎么做?谢谢.
SELECT [ID],[TEXT] FROM TBL
from tbl
from tbl
------------------------------ -----------
quicktestandbesttest 2(所影响的行数为 1 行)
参数数据类型 text 对于 len 函数的参数 1 无效。
不会吧 转换了还不行 你的SQL版本是?
select [ID],[TEXT],(len(convert(varchar(max),[TEXT]))-len(replace(convert(varchar(max),[TEXT]),'test','')))/4 as test_nums
from TBL
insert into @t
select 1,'test123test124' union all
select 2,'test134' union all
select 3,'best' union all
select 4,'hitest' union all
select 5,'quicktestandbesttest'select col,[count]=
(len(cast(col as varchar(max)))-
len(replace(cast(col as varchar(max)),'test','')))/4 from @t
/*
col count
---------------------- --------------------
test123test124 2
test134 1
best 0
hitest 1
quicktestandbesttest 2
*/