asked on

SQL Variable Problem

Hi All,

I have below script :

DECLARE @POSITIONS VARCHAR(100) = '''SLF'', ''SRF'''

select @POSITIONS as POSITIONS
 
 SELECT 
 A.PositionCode
 FROM TMPOSITION A
 WHERE A.PositionCode IN ('SLF', 'SRF')

  SELECT 
 A.PositionCode
 FROM TMPOSITION A
 WHERE A.PositionCode IN (@POSITIONS)

Open in new window

The result :

POSITIONS
----------------------------------------------------------------------------------------------------
'SLF', 'SRF'

(1 row(s) affected)

PositionCode
------------
SLF 
SRF 

(2 row(s) affected)

PositionCode
------------

(0 row(s) affected)

Open in new window

What is wrong with @Positions ?

Thank you.

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Éric Moreau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

emi_sastra

ASKER

Hi Stefan,

It does not work with your code.

DECLARE @POSITIONS VARCHAR(100) =  '/SLF/SRF/';

select @POSITIONS as POSITIONS
 
 SELECT 
 A.PositionCode
 FROM TMPOSITION A
 WHERE A.PositionCode IN ('SLF', 'SRF')

  SELECT 
 A.PositionCode
 FROM TMPOSITION A
 WHERE CHARINDEX('/' + A.PositionCode + '/', @POSITIONS) > 0;

Open in new window

Thank you.

SOLUTION

Éric Moreau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

emi_sastra

ASKER

Hi Eric,

Using RTRIM works.

Thank you very much for your help.

emi_sastra

ASKER

Hi Stefan and Eric,

Thank you very much for your help.