Solved

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

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

Expert Comment

by:Pawan Kumar Khowal
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 17

Expert Comment

by:Pawan Kumar Khowal
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Server 2008 - Issue with LIKE function 2 24
Write a function 5 24
t-sql splitting string column 5 26
Sql query 34 19
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video discusses moving either the default database or any database to a new volume.

758 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

20 Experts available now in Live!

Get 1:1 Help Now