Most efficient way of search for only spaces strings

Question

I'm consuming data from a view. The view is large (millions of rows). I need to know if a specific varchar column rows are composed completely of spaces (" ") or if it has words on it.

Right now I'm trying to limit between dates to make the query lighther as well as returning TOP 100 entrances and using LTRIM and LEN to see if there's a 0 len string (meaning just spaces)

SELECT DISTINCT TOP (100) LEN(LTRIM([col]))
FROM [data].[db_view].[vw_cta]
WHERE [date] between '2023-01-01' AND '2023-01-17'

I'm pretty sure this is not the most efficient way. Any help to speed up the query?

Answer 1

SQL Server allows storing empty strings and strings composed entirely of spaces. But SQL Server also ignores trailing spaces in varchar comparisons.

So just compare the column to '', which will match any row that has only spaces in that column, which can use an index, and is cheaper than evaluating an expression for each row. eg

SELECT DISTINCT TOP (100) LEN(LTRIM([col]))
FROM [data].[db_view].[vw_cta]
WHERE [date] between '2023-01-01' AND '2023-01-17'
  AND col = ''