Store multiple strings efficient in sql

Question

Store multiple strings efficient in sql

NikosV

SSCrazy

Points: 2111
More actions
August 20, 2018 at 2:30 am

#378297

I have 157 values (varchar) in a table but I am only interested in say 50 of these strings. So it's a simple where clause basically but I don't want to do 50 AND's, so I'm thinking about an IN operator on the where. Thing is I have two CTE's and I want to do this in BOTH CTE's.
Is there any way of storing all these values in something like an array and reusing it wherever I want in SQL Server?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182910 More actions · Answer 1

NikosV - Monday, August 20, 2018 2:30 AM
I have 157 values (varchar) in a table but I am only interested in say 50 of these strings. So it's a simple where clause basically but I don't want to do 50 AND's, so I'm thinking about an IN operator on the where. Thing is I have two CTE's and I want to do this in BOTH CTE's.
Is there any way of storing all these values in something like an array and reusing it wherever I want in SQL Server?

Can you please post the DDL (create table) script, sample data as an insert statement and the desired results!
😎

drew.allen SSC Guru Points: 76984 More actions · Answer 2

NikosV - Monday, August 20, 2018 2:30 AM
I have 157 values (varchar) in a table but I am only interested in say 50 of these strings. So it's a simple where clause basically but I don't want to do 50 AND's, so I'm thinking about an IN operator on the where. Thing is I have two CTE's and I want to do this in BOTH CTE's.
Is there any way of storing all these values in something like an array and reusing it wherever I want in SQL Server?

Yes, it's called a table. You can either store the values you're interested in in a table, temp table, or table variable or you can add a column to the current table to flag the ones that you are interested in.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

NikosV SSCrazy Points: 2111 More actions · Answer 3

drew.allen - Monday, August 20, 2018 7:28 AM
NikosV - Monday, August 20, 2018 2:30 AM
I have 157 values (varchar) in a table but I am only interested in say 50 of these strings. So it's a simple where clause basically but I don't want to do 50 AND's, so I'm thinking about an IN operator on the where. Thing is I have two CTE's and I want to do this in BOTH CTE's.
Is there any way of storing all these values in something like an array and reusing it wherever I want in SQL Server?
Yes, it's called a table. You can either store the values you're interested in in a table, temp table, or table variable or you can add a column to the current table to flag the ones that you are interested in.
Drew

Thanks!