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?

CodePudding user response：

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.

CodePudding user response：

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.

CodePudding user response：

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

CodePudding user response：

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.