Efficiently Counting Spaces using T-SQL

Let’s assume you have a table called Person and it has a column called FullName. As you might expect, the FullName is really just a concatenation of the persons first and last name separated by a space. If I want to get some stats on how many people have only one space (implying two words only) it is actually quite easy to figure this out. Consider the following SQL statement

 

select FullName from Person

Let pretend it returns something like

Ben Wheel
James Johnson Jr.
Jane Dela Cruz
Jeff Jones
Lance Strong

Exactly 1 Space

To get all rows that have exactly one space in them you could do the following

select FullName from Person
where len(FullName) - len(Replace(FullName, ' ', '')) = 1

Jeff Jones
Lance Strong
Ben Wheel

Exactly 2 Space

To get all rows that have exactly one space in them you could do the following

select FullName from Person
where len(FullName) - len(Replace(FullName, ' ', '')) = 2

James Johnson Jr.
Jane Dela Cruz

Less than or equal to 3 Space

To get all rows that have exactly one space in them you could do the following

select FullName from Person
where len(FullName) - len(Replace(FullName, ' ', '')) <= 3

Ben Wheel
James Johnson Jr.
Jane Dela Cruz
Jeff Jones
Lance Strong

As you can see it is relatively easy to count occurrences of a character using T-SQL. You can expand on these queries just by changing the stuff in red.