Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2016-11-01
8
Medium Priority
?
34 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 32

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 32

Expert Comment

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

Regards,
Pawan
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
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…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

597 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