[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


SQL query that takes an array of numbers

Posted on 2012-08-24
Medium Priority
Last Modified: 2012-08-25

I need a SQL query that takes an array of numbers such as (see query below)
How can I do use an array of different numbers in a query???

Thank you!

 public DataTable MyQuery(string x, string y, array of numbers)
            string sql = "SELECT x,y, array";
            SqlDataAdapter da = new SqlDataAdapter(sql, cnstr);
            DataTable dt = new DataTable();
            return dt;

Open in new window

Question by:Rad1
LVL 16

Expert Comment

ID: 38331870
Relational databases don't normally admit of "arrays" as such - they're called "tables" :) Can you explain what you're trying to achieve, and maybe we can come up with a good way of getting from where you are to there.
LVL 25

Accepted Solution

Rouchie earned 2000 total points
ID: 38332030
You can pass the array as an string, e.g. "1,5,6,7" and then using a SQL function to split it into a table.  There are loads of examples here:


Here's a very quick one.  You might need to add detection for empty values

DECLARE @separator char(1)
SET @separator = ','

DECLARE @array nvarchar(max)
SET @array = '1,5,7,56,4,5,7,4564,45,5456,4,65,456,4'

DECLARE @separator_position INT
DECLARE @array_value nvarchar(max)

DECLARE @tbl TABLE(ID INT IDENTITY(1,1), dateAttained INT)

WHILE patindex('%' + @separator + '%', @array) <> 0
		SELECT @separator_position =  patindex('%' + @separator + '%' , @array)
		SELECT @array_value = LEFT(@array, @separator_position - 1)
		INSERT INTO @tbl(dateAttained) VALUES (@array_value)
		SELECT @array = stuff(@array, 1, @separator_position, '')


Open in new window


Author Closing Comment

ID: 38332216

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question