SQL count times变量出现在行的值中(SQL count times variable appears in a row's value)

SQL count times变量出现在行的值中(SQL count times variable appears in a row's value)

所以，我想知道是否可以计算变量在表中使用SQL出现的次数。

示例表：

ID TextColumn 1 foo 2 foo foo foo 3 bar $var = "foo";

返回$var和$var order的所有列，从最高到最低。

示例结果：

(1)ID=2,count=3 (2)ID=1,count=1

这可能只使用SQL吗？

我之前问了一个类似的问题，但不清楚，所以如果这个问题也不清楚，请告诉我。

So, I'm wondering if it's possible to count how many times a variable appears in table using SQL.

Example table:

ID TextColumn 1 foo 2 foo foo foo 3 bar $var = "foo";

Return all columns with $var and count of $var order by highest to lowest.

Example result:

(1)ID=2,count=3 (2)ID=1,count=1

Is this possible to do using only SQL?

I asked a similiar question earlier but it wasn't clear, so if this one is also unclear please let me know.

最满意答案

如果您使用的是SQL Server，则可以使用LEN和REPLACE执行此操作：

DECLARE @var VARCHAR(MAX) = 'foo' SELECT *, (LEN(TextColumn) - LEN(REPLACE(TextColumn, @var, ''))) / LEN(@var) FROM tbl WHERE CHARINDEX(@var, TextColumn) > 0 ORDER BY (LEN(TextColumn) - LEN(REPLACE(TextColumn, @var, ''))) / LEN(@var) DESC

SQL小提琴

If you're using SQL Server, you can do this using LEN and REPLACE:

DECLARE @var VARCHAR(MAX) = 'foo' SELECT *, (LEN(TextColumn) - LEN(REPLACE(TextColumn, @var, ''))) / LEN(@var) FROM tbl WHERE CHARINDEX(@var, TextColumn) > 0 ORDER BY (LEN(TextColumn) - LEN(REPLACE(TextColumn, @var, ''))) / LEN(@var) DESC

SQL Fiddle