CODEWITHSUNDEEP
sqlsql-server
T. Doe
T. Doe

Reputation: 3

How do I group by a value which is a substring in a column?

Example: column name is ID.

The text of each column is like ... user=##### ... (where ... is some other random text, and ##### is some 5-digit number)

And I want to count the number of times each ##### appears (count descending)

Seems like this shouldn't be so hard, I'm probably missing something?

Upvotes: 0

Views: 50

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521279

One viable option might be to take a substring of the ID column which is five characters long and which begins after the occurrence of user=:

SELECT
    SUBSTRING(ID, CHARINDEX('user=', ID) + 5, 5),
    COUNT(*) AS cnt
FROM yourTable
GROUP BY
    SUBSTRING(ID, CHARINDEX('user=', ID) + 5, 5);

Upvotes: 2

Related Questions