SQL TSQL - 如何查找列是否有空格字符（32）？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1181004/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
TSQL - how to find if a column has a space char(32)?
提问by
TSQL - how to find if a column has a space char(32)?
TSQL - 如何查找列是否有空格char(32)？
select *
from [sometable]
where CHARINDEX(' ', [somecolumn]) > 0
doesn't work? Any ideas?
不起作用？有任何想法吗？
回答by Joe
You have to rtrim CHAR columns.
您必须 rtrim CHAR 列。
CHAR columns are padded with spaces on the right up to the maximum length.
CHAR 列在右边用空格填充到最大长度。
RTRIM helps to avoid false positives when storing strings that are shorter than the maximum length.
RTRIM 有助于在存储短于最大长度的字符串时避免误报。
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
回答by John Sansom
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;
回答by A-K
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 '
回答by Pratheeskumar
The following worked for me:
以下对我有用：
PrdPicture LIKE '%'+char(160)+'%'
回答by pradeep
Use below query:
使用以下查询：
SELECT UniqueName
FROM employee
WHERE employeeName LIKE '% %'
回答by Xanderak
I had the exact same question as worded, though I was looking to see if the column had a space vs being null. This worked for me:
我有与措辞完全相同的问题，尽管我想看看该列是否有空格与空值。这对我有用：
SELECT UniqueName
FROM employee
WHERE ascii(employeeName) = 32
The intuitive query doesn't work; below returns all null rows as well:
直观的查询不起作用；下面也返回所有空行：
SELECT UniqueName
FROM employee
WHERE employeeName = ' '
回答by gbn
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
你在找hardspace (nbsp)，CHAR(160)？或者tab CHAR(9)？这些看起来像空格但不是
回答by KellCOMnet
select * from [sometable] where somecolumn like '% %'
