So I need to filter column which contains either one, two or three whitespace character.
CREATE TABLE a
(
[col] [char](3) NULL,
)
and some inserts like
INSERT INTO a VALUES (' ',' ', ' ')
How do I get only the row with one white space?
Simply writing
SELECT *
FROM a
WHERE column = ' '
returns all rows irrespective of one or more whitespace character.
Is there a way to escape the space? Or search for specific number of whitespaces in column? Regex?
5条答案
按热度按时间jei2mxaa1#
Use like clause - eg
where column like '%[ ]%'
the brackets are important, like clauses provide a very limited version of regex. If its not enough, you can add a regex function written in C# to the DB and use that to check each row, but it won't be indexed and thus will be very slow.
The other alternative, if you need speed, is to look into full text search indexes.
z0qdvdin2#
Here is one approach you can take:
RETURNS
You need to use
DATALENGTH
asLEN
ignores trailing blank spaces, but this is a method I have used before.NOTE:
This example assumes the use of a
varchar
column.zbwhf8kr3#
Trailing spaces are often ignored in string comparisons in SQL Server. They are treated as significant on the LHS of the
LIKE
though.To search for values that are exactly one space you can use
Note with your example table all the values will be padded out to three characters with trailing spaces anyway though. You would need a variable length datatype (
varchar
/nvarchar
) to avoid this.The advantage this has over checking value +
DATALENGTH
is that it is agnostic to how many bytes per character the string is using (dependant on datatype and collation)DB Fiddle
qgelzfjb4#
How to get only rows with one space?
SELECT * FROM a WHERE col LIKE SPACE(1) AND col NOT LIKE SPACE(2) ;
Though this will only work for variable length datatypes.
jbose2ul5#
well u can Use like clause - eg where column like '%[ ]%'
the brackets are important, like clauses provide a very limited version of regex. If its not enough, you can add a regex function written in C# to the DB and use that to check each row, but it won't be indexed and thus will be very slow.
The other alternative, if you need speed, is to look into full text search indexes.