如何使用TSQL在varchar中计算varchar [英] How do I count varchar in a varchar using TSQL

问题描述

计算varchar中varchar的出现的最佳方法是什么.

What is the best way to count the occurence of a varchar within a varchar.

我宁愿不遍历文本来查找某些组合.

I rather not loop through a text in order to find certain combinations.

此选择仅找到第一个

SELECT CASE WHEN CHARINDEX('!','HOW MANY TIMES IS ! IN THIS TEXT ? THIS IS MY QUESTION !' ) > 0 THEN 1 ELSE 0 END

返回1

我需要一种方法来查找匹配总数

I need a method to find the total number of matches

TABLE DATA
SEARCHTEXT     LONGTEXT
!              HOW MANY TIMES IS ! IN THIS TEXT ? THIS IS MY QUESTION !
HELLO          HELLO HELLO HELLO HELLO HELLO HELLO
L              HELLO HELLO HELLO HELLO HELLO HELLO
e              more testdata

预期结果

Count SEARCHTEXT  LONGTEST
2     !           MANY TIMES IS ! IN THIS TEXT ? THIS IS MY QUESTION !
6     HELLO       HELLO HELLO HELLO HELLO HELLO HELLO
12    L           HELLO HELLO HELLO HELLO HELLO HELLO
2     e           more testdata

使用mssql server 2005

Using mssql server 2005

推荐答案

您可以使用replace将每次出现的时间延长1个字符，并计算添加的字符数:

You could use replace to make each occurrence 1 character longer, and count the number of added characters:

select  len(replace(txt, search, search + '#')) - len(txt) as Count
,       *
from    YourTable

SE Data的完整示例.

这篇关于如何使用TSQL在varchar中计算varchar的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！