Solved

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

Posted on 2016-11-01
8
28 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
[X]
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
  • 3
  • 3
  • 2
8 Comments
 
LVL 49

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 29

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 29

Expert Comment

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

Regards,
Pawan
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

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 49

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 49

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

690 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