CODEWITHSUNDEEP
sql-serversql-server-2008
Mackija
Mackija

Reputation: 317

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

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 ')

Upvotes: 1

Views: 57

Answers (3)

Prabhat G
Prabhat G

Reputation: 3029

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'+'|')

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82524

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)

Upvotes: 1

Lee Mac
Lee Mac

Reputation: 16015

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 '% '

Upvotes: 2

Related Questions