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.

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.

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

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.

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.