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=1Is 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) DESCSQL小提琴
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) DESCSQL Fiddle
更多推荐
发布评论