Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

ms sql 2008 I need to retrieve the first 3 values in a comma delimited list

Posted on 2016-11-01
8
26 Views
Last Modified: 2016-11-13
ms sql 2008 I need to retrieve the first 3 values in a comma delimited list.

The value in the field can be A or A,B or A,B,C or A,B,C,D, up to 10 values

I want to return the first, second and third values separately to use in a join

this sort of works for the first value Left(tblAuctions.auction_discipline, CharIndex('','', tblAuctions.auction_discipline + '','')-1) as first

but no luck with the second or third

Thanks
0
Comment
Question by:stucal
  • 3
  • 3
  • 2
8 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41868087
I suggest you create a "splitstring" table valued function for this, which you will most likely find useful for all sorts of stuff as well.

also see: http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28272656.html#a39589088

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[SplitStringIntoTable]  (
    @string varchar(8000),
    @delimiter varchar(5)
    )
RETURNS TABLE WITH SCHEMABINDING 
AS
--SELECT * FROM (SELECT 'ab/c/def/ghijklm/no/prq/////st/u//' AS string) AS test_values CROSS APPLY DBA.dbo.SplitStringIntoTable(test_values.string, '/')
RETURN
--Inline-CTE-Driven "tally table" produces values from 0 to 9999  ... enough to cover varchar(8000)
WITH cteDigits AS (
    SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
    SELECT [1000s].digit * 1000 + [100s].digit * 100 + [10s].digit * 10 + [1s].digit AS num
    FROM cteDigits [1s] --0 thru 9
    CROSS JOIN cteDigits [10s] --thru 99
    CROSS JOIN cteDigits [100s] --thru 999
    CROSS JOIN cteDigits [1000s] --thru 9999
),
cteBase(num) AS ( --Limit the number of rows up front, for both a performance gain and prevention of accidental "overruns"
        SELECT 0 UNION ALL
        SELECT TOP (DATALENGTH(ISNULL(@string, 1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM cteTally
), 
cteStart(num1) AS ( --This returns num + 1 (starting position of each "value" just once for each delimiter)
        SELECT b.num + 1
        FROM cteBase b
        WHERE (SUBSTRING(@string, b.num, 1) = @delimiter OR b.num = 0)
)
--Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found. 
SELECT ROW_NUMBER() OVER(ORDER BY s.num1) AS value_seq,
       LTRIM(RTRIM(SUBSTRING(@string, s.num1, ISNULL(NULLIF(CHARINDEX(@delimiter, @string, s.num1), 0) - s.num1, 8000)))) AS value
FROM cteStart s;

GO

Open in new window


That function can be used in a cross apply and then you will be able to join.

Have to leave the keyboard right now.

{+edit}
an interactive demo can be found here http://rextester.com/BCZIG27948
declare @tblAuctions table
    ([auction_discipline] varchar(20))
;
    
INSERT INTO @tblAuctions
    ([auction_discipline])
VALUES
    ('A'),
    ('A,B'),
    ('A,B,C'),
    ('A,B,C,D,5,6,7,8,9,10')
;


declare @tblOther table
    ([discipline_fk] varchar(8), [other_column] varchar(20))
;
    
INSERT INTO @tblOther
    ([discipline_fk],[other_column])
VALUES
    ('A', 'one'),
    ('B', 'two'),
    ('C', 'three'),
    ('D', 'four')
;


select
*
from @tblAuctions a
cross apply  dbo.SplitStringIntoTable (a.auction_discipline, ',' ) ca
inner join @tblOther b on ca.value = b.discipline_fk and ca.value_seq < 4

Open in new window

sample result:
    auction_discipline  value_seq value    discipline_fk    other_column
    A                       1      A       A    one
    A,B                     1      A       A    one
    A,B                     2      B       B    two
    A,B,C                   1      A       A    one
    A,B,C                   2      B       B    two
    A,B,C                   3      C       C    three
    A,B,C,D,5,6,7,8,9,10    1      A       A    one
    A,B,C,D,5,6,7,8,9,10    2      B       B    two
    A,B,C,D,5,6,7,8,9,10    3      C       C    three

Open in new window

0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41868119
Try...

--

--Create table
 
CREATE TABLE TestCommaUsingCrossApply
(
ID INT
,VALUE VARCHAR(100)
)
GO
 
--Insert Data
 
INSERT INTO TestCommaUsingCrossApply(ID,VALUE)
VALUES
(1,'a,b,c'),
(2,'s,t,u,v,w,x')
 
--Verify Data
select ID,VALUE from TestCommaUsingCrossApply

SELECT Id,SplitedValue FROM
(
    SELECT ID,cast(('<X>'+replace(e.VALUE,',' ,'</X><X>')+'</X>') as xml) as xmlcol  FROM  TestCommaUsingCrossApply e 
) s
OUTER APPLY
(
    SELECT ProjectData.D.value('.', 'varchar(100)') as SplitedValue
    FROM s.xmlcol.nodes('X') as ProjectData(D)
) a 

--

Open in new window


O/p

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

Id      SplitedValue
1      a
1      b
1      c
2      s
2      t
2      u
2      v
2      w
2      x

If you want only 3 rows per Id then use below

SELECT Id,SplitedValue FROM  
(
	SELECT Id,SplitedValue , ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT NULL)) rnk FROM
	(
		SELECT ID,cast(('<X>'+replace(e.VALUE,',' ,'</X><X>')+'</X>') as xml) as xmlcol  FROM  TestCommaUsingCrossApply e 
	) s
	OUTER APPLY
	(
		SELECT ProjectData.D.value('.', 'varchar(100)') as SplitedValue
		FROM s.xmlcol.nodes('X') as ProjectData(D)
	) a
)k WHERE k.rnk < 4

Open in new window


O/p
---
Id      SplitedValue
1      a
1      b
1      c
2      s
2      t
2      u
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41871603
Hi stucal,
A feedback will be appreciated.

Regards,
Pawan
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Accepted Solution

by:
stucal earned 0 total points
ID: 41878578
Thanks for the solutions guys, but given the complexity with having to create dynmic tables i have decided to do this in the function instead of using sql, far easier. Seems a hole in ms sql that does not provide a better string manipulation solution.

thanks

stu
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41879062
I proposed the use of a function.

The function i proposed is very efficient.

I then also showed how to incorporate using a table valued function in a a query.

What function did you use?
0
 

Author Comment

by:stucal
ID: 41879140
yes thanks PaulPromotingPrefix EE

I took the field and did the split using c# string functions, not sql. probably should have done this initially but thought there maybe a simple sql string function to do it
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41879355
Ok. thanks. Such facts help if others read this question, and let's us know what else we can suggest.
0
 

Author Closing Comment

by:stucal
ID: 41885228
did nit use sqk for this solution
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

839 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