SQL Server : find rows that contains spaces

Question

I'm looking for a fast way to see all rows in my table that contain any spaces.

For starters, I tried to see which rows starts with a space with this query:

select * 
from MyTable 
where ColumnName like ' %'

but I got 0 results, although I can see there are rows with spaces.

Answer 1

In SQL server you can use this:

SELECT * FROM MYTABLE 
WHERE CHARINDEX(' ',ColumnName) > 0;

If you are using Oracle you can use this:

SELECT * FROM MYTABLE 
WHERE INSTR(ColumnName,' ') > 0;

Essentially in this query it finds the character position containing first space from the column values and when it finds the first space in it the index value should be greater than 1 and it should display all the records based on that.