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!
Leave a Reply