CODEWITHSUNDEEP
sqlsql-serverssms
r.tom
r.tom

Reputation: 105

SQL Server : find rows that contains spaces

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

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

select * 
from MyTable 
where ColumnName like ' %'

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

Upvotes: 8

Views: 47148

Answers (4)

Manohar Tharanath
Manohar Tharanath

Reputation: 1

select * from TABLE where trim(column_name) IS NULL

Upvotes: -2

Ismail Elias
Ismail Elias

Reputation: 31

try put another '%', this may solve your problem.

select * from MyTable where ColumnName like '% %'

Upvotes: 3

KumarHarsh
KumarHarsh

Reputation: 5094

Seem that those space contain some other special character apart from char(10).

Try this,

select * 
from MyTable 
where patindex('%[^A-Z0-9]%',ColumnName )>0

First decide what is valid value for ColumnName .Then try PATINDEX and change the regex accordingly.

Upvotes: 3

Sunil Cyriac
Sunil Cyriac

Reputation: 523

Give this a try:

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.

Upvotes: 17

Related Questions