-2

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ashutosh Ranjan
  • 1
  • 1
  • While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). – Yitzhak Khabinsky Dec 06 '22 at 16:02
  • 2
    FYI, that `INSERT` statement will error, as you are trying to provide 3 columns to the table, but it only contains 1. – Thom A Dec 06 '22 at 16:27
  • If your column is `[char](3)` they will all be exactly three spaces anyway. It is still correct though that trailing spaces are ignored in `=` so this doesn't address all your issues – Martin Smith Dec 06 '22 at 16:28
  • Are you looking for rows which contains 1 plus spaces, or only rows that contain spaces here? – Thom A Dec 06 '22 at 16:29
  • Assuming your insert were to work correctly, being a *char* data type, all 3 rows would contain 3 space characters regardless. – Stu Dec 06 '22 at 16:38

5 Answers5

0

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.

gbjbaanb
  • 51,617
  • 12
  • 104
  • 148
  • Thanks for responding. But it does not work – Ashutosh Ranjan Dec 06 '22 at 16:12
  • "Does not work" doesn't help us help you, @AshutoshRanjan . The solution seems to work why I try it: [db<>fiddle](https://dbfiddle.uk/HVRKn-Eg) it returns every row apart from `'abc'` as that's the only row that doesn't contain whitespace. – Thom A Dec 06 '22 at 16:30
  • 1
    "full text search indexes" are completely inappropriate for looking for spaces – Martin Smith Dec 06 '22 at 16:44
0

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

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
0

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.

critical_error
  • 6,306
  • 3
  • 14
  • 16
0

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.

Glasses93
  • 1
  • 1
  • 1
0

Thanks guys for answering.

So I converted the char(3) column to varchar(3).

This seemed to work for me. It seems sql server has ansi padding that puts three while space in char(3) column for any empty or single space input. So any search or len or replace will take the padded value.

Ashutosh Ranjan
  • 1
  • 1