如何使用TSQL在varchar中计算varchar

本文介绍了如何使用TSQL在varchar中计算varchar的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

计算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的完整示例.