CODEWITHSUNDEEP
sqlsql-server
Sarfaraz Makandar
Sarfaraz Makandar

Reputation: 6733

Check string in select statement using SQL Server 2008

Here I need to check the string which is in the format 'a,c,e'. This situation occurred when user select multiple option from check box and in the query I need to check it with the present column.

Example:

Given string: 

'a,c,e'

Need to check the given string each word is present in the column columnA or not:

columnA    columnB
-------------------
a            1
b            2
c            3
d            4
e            5
f            6
g            7
h            8

If a,c,e present in the column columnA it should retrieve with the columnB:

Expected Result:

columnA   columnB
------------------
a            1
c            3
e            5

My try:

select columnA,columnB from 
test where columnA ='a' 
or columnA = 'c' 
or columnA = 'e'

I don't feel! this is the optimize way to do so. And this is also not good for the dynamic query where the string values become changes concurrently.

Upvotes: 2

Views: 115

Answers (4)

Low Chee Mun
Low Chee Mun

Reputation: 610

try to make ur life easier, put your input string before column in select statement.

declare @temp table
(
ColumnA NVARCHAR(MAX),
ColumnB NVARCHAR(MAX)
)


insert into @temp
select 'a','1'
union all
select 'b','2'
union all
select 'c','3'
union all
select 'd','4'
union all
select 'e','5'
union all
select 'f','6'
union all
select 'g','7'

DECLARE @input NVARCHAR(MAX) 
SET @input= 'a,d,c'

select * from @temp where @input like '%' + ColumnA +'%'

Upvotes: 0

Dhwani
Dhwani

Reputation: 7626

Try below code:

DECLARE @COMMASEPSTR NVARCHAR(200), @SQL nvarchar(max), @STR  nvarchar(100) = 'a,b,c'
 SET @COMMASEPSTR= '''' + REPLACE(@STR,',',''',''') + ''''

SET @SQL = 'select columnA,columnB from 
test where columnA IN ( ' + @COMMASEPSTR + ')'
EXEC sp_executesql @SQL

Tell me if I am wrong somewhere.

Upvotes: 2

masum7
masum7

Reputation: 832

I am not sure about the format of your dynamic input string or format of columnA but you can try this:

select columnA,columnB from 
test where CHARINDEX(columnA, 'a,c,e')>0

Upvotes: 2

Geoduck
Geoduck

Reputation: 8995

You can use the IN syntax:

select columnA,columnB from 
test where columnA IN ('a','b','c')

Upvotes: 1

Related Questions