Comma Delimited SQL string Need to separated
It think the easiest way to do it, will be, dynamic SQL generation:
// assuming select is a SqlCommand
string[] values = "A,B,C,D,E,F".Split(',');
StringBuilder query = new StringBuilder();
query.Append("select * from tbl_test where tbl_test.code in (");
int i = 0;
foreach (string value in values) {
string paramName = "@p" + i++;
query.Append(paramName);
select.Parameters.AddWithValue(paramName, value);
}
query.Append(")");
select.CommandText = query.ToString();
// and then execute the select Command
How to split a comma-separated value to columns
CREATE FUNCTION [dbo].[fn_split_string_to_column] (
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @out_put TABLE (
[column_id] INT IDENTITY(1, 1) NOT NULL,
[value] NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @value NVARCHAR(MAX),
@pos INT = 0,
@len INT = 0
SET @string = CASE
WHEN RIGHT(@string, 1) != @delimiter
THEN @string + @delimiter
ELSE @string
END
WHILE CHARINDEX(@delimiter, @string, @pos + 1) > 0
BEGIN
SET @len = CHARINDEX(@delimiter, @string, @pos + 1) - @pos
SET @value = SUBSTRING(@string, @pos, @len)
INSERT INTO @out_put ([value])
SELECT LTRIM(RTRIM(@value)) AS [column]
SET @pos = CHARINDEX(@delimiter, @string, @pos + @len) + 1
END
RETURN
END
Turning a Comma Separated string into individual rows
You can use the wonderful recursive functions from SQL Server:
Sample table:
CREATE TABLE Testdata
(
SomeID INT,
OtherID INT,
String VARCHAR(MAX)
);
INSERT Testdata SELECT 1, 9, '18,20,22';
INSERT Testdata SELECT 2, 8, '17,19';
INSERT Testdata SELECT 3, 7, '13,19,20';
INSERT Testdata SELECT 4, 6, '';
INSERT Testdata SELECT 9, 11, '1,2,3,4';
The query
WITH tmp(SomeID, OtherID, DataItem, String) AS
(
SELECT
SomeID,
OtherID,
LEFT(String, CHARINDEX(',', String + ',') - 1),
STUFF(String, 1, CHARINDEX(',', String + ','), '')
FROM Testdata
UNION all
SELECT
SomeID,
OtherID,
LEFT(String, CHARINDEX(',', String + ',') - 1),
STUFF(String, 1, CHARINDEX(',', String + ','), '')
FROM tmp
WHERE
String > ''
)
SELECT
SomeID,
OtherID,
DataItem
FROM tmp
ORDER BY SomeID;
-- OPTION (maxrecursion 0)
-- normally recursion is limited to 100. If you know you have very long
-- strings, uncomment the option
Output
SomeID | OtherID | DataItem
--------+---------+----------
1 | 9 | 18
1 | 9 | 20
1 | 9 | 22
2 | 8 | 17
2 | 8 | 19
3 | 7 | 13
3 | 7 | 19
3 | 7 | 20
4 | 6 |
9 | 11 | 1
9 | 11 | 2
9 | 11 | 3
9 | 11 | 4
SQL use in query for comma separated string
Just use a subquery:
select *
from mytable
where fruit in (select value from STRING_SPLIT(@MyStr, ','));
In older versions of SQL Server, you can use like
:
select *
from mytable
where ',' + @MyStr + ',' like '%,' + fruit + ',%';
Need comma separated value in table in SQL Server 2014
You're part of the way there. The tradition method is using STUFF
:
SELECT t.id, t.firstname,
STUFF((SELECT ', ' + sq.lastname
FROM #temp sq
WHERE sq.id = t.id
AND sq.firstname = t.firstname
ORDER BY sq.lastname
FOR XML PATH('')),1,1,'') AS lastname
FROM #temp t
GROUP BY t.id, t.firstname;
There are lots of answers on SO already on how to do this though, but you have shown effort. :)
Comma-delimited string
This is a bit convoluted method.
But it can transform the string in the format of a JSON array.
Then it'll be easier to use JSON functions on it.
The example uses a UDF fnPattern_Split
that accepts a pattern to unnest the string.
The source code can be found here
declare @str varchar(max), @js varchar(max);
set @str ='Value1,Value2,"Value3,Value4,Value5",Value6';
;with cte1 as (
select *
from dbo.fnPattern_Split(@str,'"%"') ps
)
, cte2 as (
select ordinal as ord1, 0 as ord2, value
from cte1
where match = 1
union all
select c.ordinal, ps.ordinal, quotename(ps.value,'"') as value
from cte1 c
cross apply fnPattern_Split(c.value,',') ps
where c.match = 0 and ps.match = 0
)
select @js = '['+string_agg(value, ',')
within group (order by ord1, ord2)+']'
from cte2;
print(@js);
select *
from OPENJSON(@js) js;
key | value | type |
---|---|---|
0 | Value1 | 1 |
1 | Value2 | 1 |
2 | Value3,Value4,Value5 | 1 |
3 | Value6 | 1 |
Comma separated results in SQL
Update (As suggested by @Aaron in the comment)
STRING_AGG is the preferred way of doing this in the modern versions of SQL Server (2017 or later). It also supports easy ordering.
SELECT
STUDENTNUMBER
, STRING_AGG(INSTITUTIONNAME, ', ') AS StringAggList
, STRING_AGG(INSTITUTIONNAME, ', ') WITHIN GROUP (ORDER BY INSTITUTIONNAME DESC) AS StringAggListDesc
FROM Education E
GROUP BY E.STUDENTNUMBER;
Original Answer:
Use FOR XML PATH('')
- which is converting the entries to a comma separated string and STUFF() -which is to trim the first comma- as follows Which gives you the same comma separated result
SELECT
STUFF((SELECT ',' + INSTITUTIONNAME
FROM EDUCATION EE
WHERE EE.STUDENTNUMBER = E.STUDENTNUMBER
ORDER BY sortOrder
FOR XML PATH(''), TYPE).value('text()[1]', 'nvarchar(max)')
, 1, LEN(','), '') AS XmlPathList
FROM EDUCATION E
GROUP BY E.STUDENTNUMBER
Here is the FIDDLE showing results for both STRING_AGG
and FOR XML PATH('')
.
Split comma delimited string and insert to a table (int)
Using the Split()
function you have mentioned in comments,
-- Variable holding comma separated values
DECLARE @Var VARCHAR(4000);
SET @Var = '188,189,190,191,192,193,194'
-- Test Target Table
DECLARE @Target_Table TABLE (First_ID INT,Second_ID INT,Third_ID INT)
-- Insert statement
INSERT INTO @Target_Table
SELECT 1, CAST(Items AS INT) , 0
FROM dbo.Split(@Var, ',')
-- Test Select
SELECT * FROM @Target_Table
Result Set
╔══════════╦═══════════╦══════════╗
║ First_ID ║ Second_ID ║ Third_ID ║
╠══════════╬═══════════╬══════════╣
║ 1 ║ 188 ║ 0 ║
║ 1 ║ 189 ║ 0 ║
║ 1 ║ 190 ║ 0 ║
║ 1 ║ 191 ║ 0 ║
║ 1 ║ 192 ║ 0 ║
║ 1 ║ 193 ║ 0 ║
║ 1 ║ 194 ║ 0 ║
╚══════════╩═══════════╩══════════╝
Related Topics
Modify(Replace) Xml for Conditions
Choose As400 Query Records Directly from Excel
Can Scalar Functions Be Applied Before Filtering When Executing a SQL Statement
Reverse in Oracle This Path Z/Y/X to X/Y/Z
Difference Between a Inline Function and a View
Using Insert into with 'Select' to Supply Some Values But Not Others (Access 2010)
Case Statement with Different Data Type
How to Use a Function-Based Index on a Column That Contains Nulls in Oracle 10+
Local Collection Types Not Allowed in SQL Statements
Derby's Handling of Null Values
If It Is Not Allowed to Rollback a Truncate Statement Then How How to Use It in a Transaction
Is Not Null Test for a Record Does Not Return True When Variable Is Set
SQL Performance of a Lookup Table
In SQL, What Does Using Parentheses with an or Mean
SQL Server Management Studio 2012 - Export/Import Data From/To Table