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: Monday, June 30, 2014 1:23 PM
Points: 336, Visits: 866
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: 2 days ago @ 9:29 AM
Points: 2,422, Visits: 7,435
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: Monday, June 30, 2014 1:23 PM
Points: 336, Visits: 866
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 @ 7:02 PM
Points: 3,313, Visits: 7,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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

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: Monday, June 30, 2014 1:23 PM
Points: 336, Visits: 866
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 @ 7:02 PM
Points: 3,313, Visits: 7,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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

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: 2 days ago @ 9:29 AM
Points: 2,422, Visits: 7,435
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