Group By Starting Characters

I was chatting with Jeff (b|t) on my team yesterday and the context escapes me but I had this thought:

“Can you Group By the beginning characters, or a subset, of a field?”

I’m not a developer, so this question never comes my way. Except yesterday.

TL;DR – Yes.

Basic Group By code, using StackOverflow public data:

Select [Location], Count(Location)
From StackOverflow..Users
Where [Location] Like 'D%'
Group By [Location]
Having Count(Location) > 1
Order By [Location]

Same code, Grouping By the first 6 characters:

Select Left([Location],6), Count(Left(Location,6))
From StackOverflow..Users
Where [Location] Like 'D%'
Group By Left([Location],6)
Having Count(Left(Location,6)) > 1
Order by Left([Location],6)

Results. Lets pretend I was looking for people in Dallas.

There is more than 1 Dallas BTW:

I’m sure there are better use cases…but I only spent 7 minutes on this nonsense, just to see what would happen.

When find yourself saying “I wonder if SQL Server can….”, go try it.  Learn first, Google later 🙂

Thanks for reading!

Kevin3NF

Follow @Dallas_DBAs