Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SPLIT a single column value by comma Expand / Collapse
Author
Message
Posted Tuesday, October 15, 2013 8:22 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 12, 2014 11:45 AM
Points: 336, Visits: 867
Hi

I have a query that returns a column whose value I have to use in an IN clause separated by commas

for example in the below code I want to use like this where MYcolumnName IN( 2,5,6)
here I have 256 in NAME and I have separated it by commas...

CREATE TABLE #temp
( ID int,NAME char(10))

INSERT INTO #temp
SELECT 1,'256'
union all
SELECT 2,'56'
union all
SELECT 3,'2GH6'
union all
SELECT 4,'256W'
union all
SELECT 5,'256GWP'
union all
SELECT 6,'256'

SELECT * from #temp


Any help on this please..
I can't use PARSENAME function as its valid only for 4 CHAR but my string is 10 char long...
Thanks



---------------------------------------------------

Thanks
Post #1504785
Posted Tuesday, October 15, 2013 8:28 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:16 AM
Points: 2,379, Visits: 7,567
Take a look at the 8K Splitter.


Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1504786
Posted Tuesday, October 15, 2013 8:36 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 12, 2014 11:45 AM
Points: 336, Visits: 867
Thanks a lot..
but If I had to add CTE and temp table I could have done this ,Can you suggest any simple way to just add a third column and show split values separated by comma for second column in that...

Thanks



---------------------------------------------------

Thanks
Post #1504791
Posted Tuesday, October 15, 2013 8:43 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:48 PM
Points: 3,636, Visits: 8,151
Not exactly what you might be looking for, but instead of trying to add commas after each character, you can use a subquery in the IN clause. Something like this:

WITH Tally(n) AS(
SELECT *
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))x(n))
SELECT NAME, SUBSTRING( NAME, n, 1)
from #temp te
JOIN Tally ta ON LEN( te.NAME) >= ta.n

Of course, the CTE can´t be in the subquery but it should be on the outer query.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1504795
Posted Tuesday, October 15, 2013 8:55 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 12, 2014 11:45 AM
Points: 336, Visits: 867
Thank You Sir Luis ..

Your code works ...I did implement it ...
Thanks



---------------------------------------------------

Thanks
Post #1504799
Posted Tuesday, October 15, 2013 9:06 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:48 PM
Points: 3,636, Visits: 8,151
I'm glad that I could be of help. It's weird to be called Sir Luis, makes me feel like someone important.


Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1504805
Posted Tuesday, October 15, 2013 9:20 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:16 AM
Points: 2,379, Visits: 7,567
Learner1 (10/15/2013)
Thanks a lot..
but If I had to add CTE and temp table I could have done this ,Can you suggest any simple way to just add a third column and show split values separated by comma for second column in that...

Thanks


Teach a man to fish. . .

This is what I'd have done to the 8K splitter: -
CREATE FUNCTION [dbo].[DelimitedSplit8K] (@pString VARCHAR(8000))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS RETURN
WITH E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (SELECT TOP (ISNULL(LEN(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM E4),
cteStart(N1) AS (SELECT t.N
FROM cteTally t),
cteLen(N1,L1) AS(SELECT s.N1, 1
FROM cteStart s)
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l;

Here's how you'd use it on your sample data: -
SELECT NAME, Item
FROM #temp
CROSS APPLY [dbo].[DelimitedSplit8K](NAME);

Here's the returned values: -
NAME       Item
---------- -----
256 2
256 5
256 6
56 5
56 6
2GH6 2
2GH6 G
2GH6 H
2GH6 6
256W 2
256W 5
256W 6
256W W
256GWP 2
256GWP 5
256GWP 6
256GWP G
256GWP W
256GWP P
256 2
256 5
256 6



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1504816
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse