In SQL Server, how can I search for column with 1 or 2 whitespace characters?

5us2dqdw  于 2022-12-10  发布在  SQL Server
关注(0)|答案(5)|浏览(155)

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?

jei2mxaa

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.

z0qdvdin

z0qdvdin2#

Here is one approach you can take:

DECLARE @data table ( txt varchar(50), val varchar(50) );
INSERT INTO @data VALUES ( 'One Space', ' ' ), ( 'Two Spaces', '  ' ), ( 'Three Spaces', '   ' ); 
    
;WITH cte AS (
    SELECT
        txt,
        DATALENGTH ( val ) - ( DATALENGTH ( REPLACE ( val, ' ', '' ) ) ) AS CharCount
    FROM @data
) 
SELECT * FROM cte WHERE CharCount = 1;

RETURNS

+-----------+-----------+
|    txt    | CharCount |
+-----------+-----------+
| One Space |         1 |
+-----------+-----------+

You need to use DATALENGTH as LEN ignores trailing blank spaces, but this is a method I have used before.

NOTE:

This example assumes the use of a varchar column.

zbwhf8kr

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

select *
from a
where  ' ' LIKE col AND col = ' ' 
/*The second predicate is required in case col contains % or _ and for index seek*/

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

qgelzfjb

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.

jbose2ul

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.

相关问题