Solved

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

Posted on 2016-11-01
8
24 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 24

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 24

Expert Comment

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

Regards,
Pawan
0
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

867 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now