How to use IN condition in SQL Server when comparing Varchars with trailing spaces?

Question

Here is a sample of the issue:

SELECT 'True'
WHERE 'Hello      ' IN ('Hello', 'Goodbye', 'Hello  ')

Currently this returns 'True' because SQL ignores trailing spaces when comparing VARCHAR. In this related question, this can be solved by using LIKE, however this won't work with an IN condition.

How can I ensure that the comparison takes into account the trailing spaces, specifically when using the IN condition?

Edit: My list of acceptable values can include items with trailing spaces. Looking to compare exact values essentially (i.e 'Hello ' won't match with 'Hello ')

score 2 · Answer 1 · answered Nov 08 '18 at 13:26

2

Assuming that your list of acceptable values do not have trailing spaces, perhaps you could use:

SELECT 'True'
WHERE 'Hello      ' IN ('Hello', 'Goodbye') AND 'Hello      ' NOT LIKE '% '

answered Nov 08 '18 at 13:26

Lee Mac

15,615
6
32
80

Good solution but unfortunately my list does have the potential to have those trailing spaces, I will update my question – Mackija Nov 08 '18 at 13:49

score 1 · Accepted Answer · answered Nov 08 '18 at 13:29

1

You could add a non-space char to the end of your search temrs:

DECLARE @Terminator char(1) = '|';

SELECT 'True'
WHERE 'Hello      ' + @Terminator  IN ('Hello' + @Terminator , 'Goodbye' + @Terminator)

This will force the comparison to take into account the trailing spaces while keeping everything seargable. (I assume you want to use columns either on the left or on the right side of the IN operator)

answered Nov 08 '18 at 13:29

Zohar Peled

79,642
10
69
121

This is a good solution, one downside would be that I would have to add the terminator to each of items which I'd like to avoid if possible (I have a long list). Also trying to avoid putting that list into a table – Mackija Nov 08 '18 at 13:46
1

@Mackija Don't avoid putting the values in a table. I think this will be the correct course of action. – UnhandledExcepSean Nov 08 '18 at 14:20

score 1 · Answer 3 · answered Nov 08 '18 at 13:40

1

I can think of this solution on top of my head:

SELECT 'True'
WHERE reverse('Hello      ') IN (reverse('Hello'), reverse('Goodbye'))

Basically this forces to compare string using reverse function.

But Zohar's solution below is most performance driven solution.

SELECT 'True'
WHERE 'Hello      '+'|' IN ('Hello'+'|', 'Goodbye'+'|')

answered Nov 08 '18 at 13:40

Prabhat G

2,974
1
22
31

Also a good solution like Zohar's but has a similar problem with having to add something to each of the items in the IN which I can hopefully avoid – Mackija Nov 08 '18 at 13:47
@Mackija : True. But using `LIKE` will most certainly destroy your performance – Prabhat G Nov 08 '18 at 13:49
Good point, I would say for my purposes though performance isn't a huge issue. More so looking for a solution that is small and readable (if one exists that is) – Mackija Nov 08 '18 at 13:56
1

If there are spaces at the front, this won't work for the same reason it currently won't work unreversed. – UnhandledExcepSean Nov 08 '18 at 14:05
True, one work around to that would be to do the comparison both reversed and not reversed – Mackija Nov 08 '18 at 14:21
1

@UnhandledExcepSean : True, but OP has explicitly mentioned in edit about trailing spaces. – Prabhat G Nov 08 '18 at 14:22
@Mackija Except it still won't work if there are spaces on both sides of the IN. – UnhandledExcepSean Nov 08 '18 at 14:22
@UnhandledExcepSean Right because then I would have to do one on one comparisons like ' Hi ' = ' Hi ' OR ' iH ' = ' iH ' outside of the IN – Mackija Nov 08 '18 at 14:28
1

@PrabhatG To clarify yes I'm only looking at trailing spaces for my case – Mackija Nov 08 '18 at 14:30

How to use IN condition in SQL Server when comparing Varchars with trailing spaces?

3 Answers3