CODEWITHSUNDEEP
sql-servert-sql
StormRider01
StormRider01

Reputation: 463

Searching for just ? and spaces in MSSQL

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

Upvotes: 0

Views: 45

Answers (3)

Luaan
Luaan

Reputation: 63722

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.

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

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 '%[^? ]%')

Upvotes: 4

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

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    ? ?

Upvotes: 3

Related Questions