SQL count times variable appears in a row's value

Question

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.

you have tagged both mysql and sql-server... which is solution you actually need or both. — Dean
– Dean, Commented Jan 20, 2016 at 6:48
And again today: en.wikipedia.org/wiki/Database_normalization — Deep
– Deep, Commented Jan 20, 2016 at 6:48

Felix Pamittan · Accepted Answer · 2016-01-20 07:25:17Z

1

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

edited Jan 20, 2016 at 7:25

answered Jan 20, 2016 at 6:51

Felix Pamittan

31.9k7 gold badges43 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL count times variable appears in a row's value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related