TSQL - how to find if a column has a space char(32)?

Question

TSQL - how to find if a column has a space char(32)?

select * from [sometable] where CHARINDEX(' ', [somecolumn]) > 0

doesn't work? Any ideas?

Answer 1

select * from [sometable] where somecolumn like '% %'

Answer 2

What do you mean by "doesn't work"? Both ways work for me:

SELECT ''''+a+'''' FROM(
SELECT 'asd fgh' AS a UNION ALL
SELECT ' fgh' AS a UNION ALL
SELECT 'asd ' AS a UNION ALL
SELECT 'asfdg') As t
WHERE a LIKE '% %'

---------
'asd fgh'
' fgh'
'asd '

SELECT ''''+a+'''' FROM(
SELECT 'asd fgh' AS a UNION ALL
SELECT ' fgh' AS a UNION ALL
SELECT 'asd ' AS a UNION ALL
SELECT 'asfdg') As t
WHERE CHARINDEX(' ', a) > 0

---------
'asd fgh'
' fgh'
'asd '

Answer 3

You have to rtrim CHAR columns.

select * from [table] where rtrim(col) like '% %'



create table dropme
(foo char(32))

insert into dropme values('nospaces')
insert into dropme values('i have a space')
insert into dropme values('space bar')

select replace(foo,' ','|') from dropme
where foo like '% %'

---

nospaces
i|have|a|space
space|bar

select replace(foo,' ','|') from dropme
where rtrim(foo) like '% %'

---

i|have|a|space
space|bar

Answer 4

The following example should illustrate how you can achieve this.

create table #tableTest
(
    someData varchar(100) not null
);

insert into #tableTest(someData) values('dsadsa');
insert into #tableTest(someData) values('fdssf 432423');
insert into #tableTest(someData) values('432423fsdv');
insert into #tableTest(someData) values('321 jhlhkj 543');



select * 
from #tableTest;

select * 
from #tableTest 
where charindex(char(32),someData) > 0;

drop table #tableTest;

Answer 5

Given you've not really explained what the problem is...

Are you looking for hardspace (nbsp), CHAR(160)? Or tab CHAR(9)? These can look like spaces but aren't