Check string in select statement using SQL Server 2008

Question

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.

That query is fine. It it as optimal as it is going to get, if columnA has indices. Alternatively, it can be expressed as where columnA IN ('a', 'c', 'e'); but this will not make it "more optimized".. and I have no idea what "string value .. changes concurrently" is referring to. — user2864740
– user2864740, Commented Sep 9, 2014 at 4:17
The method will work if input is certain static a, c, e.. but if you are talking about dynamic input then? — masum7
– masum7, Commented Sep 9, 2014 at 4:20

Dhwani · Accepted Answer · 2014-09-09 04:24:59Z

2

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.

answered Sep 9, 2014 at 4:24

Dhwani

7,66618 gold badges82 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

masum7 · Accepted Answer · 2014-09-09 04:18:55Z

2

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

answered Sep 9, 2014 at 4:18

masum7

8326 silver badges17 bronze badges

4 Comments

user2864740 Over a year ago

This method will never be able to use indices, I wouldn't recommend it.

masum7 Over a year ago

According to question input string can vary. Means, it can be dynamic

user2864740 Over a year ago

True; I would still move the parsing of the string to an appropriate SQL statement to the application-layer. This does the advantage of not needing that (for laziness, if nothing else ;-)

masum7 Over a year ago

Better to write a function to split the string into arrays.. unfortunately SQL Server doesn't have Split like C#

Geoduck · Accepted Answer · 2014-09-09 04:16:50Z

1

You can use the IN syntax:

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

answered Sep 9, 2014 at 4:16

Geoduck

9,0212 gold badges29 silver badges27 bronze badges

Comments

Low Chee Mun · Accepted Answer · 2014-09-09 06:20:10Z

0

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

answered Sep 9, 2014 at 6:20

Low Chee Mun

6101 gold badge4 silver badges9 bronze badges

Collectives™ on Stack Overflow

Check string in select statement using SQL Server 2008

4 Answers 4

Comments

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related