Searching for just ? and spaces in MSSQL

Question

I have a varchar column that had some nvarchar data inserted, so I now have records that contain data such as '? ???'

select * from table where EnglishName LIKE '%[? ]%'

Returns everything, and other variations of LIKE '[? ]' don't return the data I'm after.

How can I select records that ONLY contain Question marks, and Optionally contain a Space?

'??' - return
'? ?' - return
' ?' - return
'? chen' - don't return
' ' - don't return
'  chen' - don't return

Answer 1

Use an additional predicate to filter out non ? or space characters:

WHERE (col LIKE '%[? ]%') AND (col NOT LIKE '%[^? ]%')

The above will also select records containing just spaces. If you want to filter these records out as well, then simply use:

WHERE (col LIKE '%[?]%') AND (col NOT LIKE '%[^? ]%') 

Answer 2

Try this:

DECLARE @t TABLE (id INT, n VARCHAR(100))
INSERT INTO @t VALUES
(1, '??'),
(2, '? ?'),
(3, ' ?'),
(4, ' ? '),
(5, '? '),
(6, ' ? ? '),
(7, '? chen??'),
(8, ' chen '),
(9, 'chen  ?? ??'),
(10, ' chen ?')


SELECT * FROM @t 
WHERE n LIKE '%[?]%' AND n NOT LIKE '%[^? ]%[?]%' AND n NOT LIKE '%[?]%[^? ]%'

Output:

id  n
1   ??
2   ? ?
3    ?
4    ? 
5   ? 
6    ? ? 

Answer 3

It seems like you're only doing this as part of a manual maintenance operation. If that's the case, this could be good enough:

where len(replace([EnglishName], '?', '')) = 0

This will replace all the ? with an empty string and take the length excluding any trailing spaces - so if the string is only ? and spaces, its length is going to be zero.

If my assumption is wrong and you're using this as part of your application proper, I'd avoid hacks like this. There probably is a more reasonable way of accomplishing this.

EDIT: To handle the additional constraint of "do not select names that are only spaces", you can just add and len([EnglishName]) > 0.