Seperate comma deliminated values

Hello Experts,

the solution to this problem may be simple but at the moment it is not forthcoming;

I have a script to separate a comma deliminator column i need to be able to use the value output to filter another view.

I initially attempted to use it as a nested select statement but it was falling over at the 'Create Unique Clustered Index '

so I attempted to create view, where the view runs and displays the results; SSME will not save it.

SELECT IDENTITY(INT) AS Number
   INTO Numbers
   FROM sysobjects s1
  CROSS JOIN sysobjects s2

CREATE UNIQUE CLUSTERED INDEX Number_ind
   ON Numbers(number)
   WITH IGNORE_DUP_KEY) 

SELECT dbo.Licence.LicencingID, dbo.Licence.PropertyID, dbo.Licence.TAG,
       LTRIM(RTRIM(SUBSTRING( dbo.Licence.TAG, Number, 
       CHARINDEX( ',', dbo.Licence.TAG + ',', Number ) - Number ))) as Value
  FROM dbo.Licence
 INNER JOIN Numbers
    ON SUBSTRING( ',' + dbo.Licence.TAG, Number, 1 ) = ',' 
where Number <= Len(dbo.Licence.TAG) + 1

Open in new window


Some guidance would be most appreciated.
LVL 7
XGISAsked:
Who is Participating?
 
PortletPaulConnect With a Mentor freelancerCommented:
oh, & Are you able to retain the table [Numbers]? That would save you lots of time/effort.
0
 
ArgentiCommented:
you have an extra ")" after IGNORE_DUP_KEY
0
 
XGISAuthor Commented:
pls note this typo is not in the syntax, thankyou though
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
PortletPaulfreelancerCommented:
How many numbers do you need for this?

Here is a post by dtodd that nicely generates 1000 rows into a numbers table with clustered index, the table is [Number] and the field [n] which can be altered of course to suit.

--
use Licence -- change to suit
go

create table dbo.Number(    
	n int constraint pk_Number primary key clustered 
	)
;

if object_id( N'tempdb..#digit', N'U' ) is not null 
	drop table #digit;
	
create table #digit(
	i int
	)

insert #digit( i ) values ( 0 ), ( 1 ), ( 2 ), ( 3 ), ( 4 ), ( 5 ), ( 6 ), ( 7 ), ( 8 ), ( 9 )
	
insert dbo.Number( n )
	select
		(( h.i * 10 ) + t.i ) * 10 + o.i
	from #digit o
	cross join #digit t
	cross join #digit h
;

	drop table #digit;

select count(distinct n) from Number;

Open in new window

(expected result 1000)
0
 
ArgentiCommented:
To me, after creating the Licence table, it's working just fine.
0
 
PortletPaulfreelancerCommented:
oh, the sequence generated starts at 0 and finishes at 999
it can be pushed up one if needed, amending line 21

      1 +      (( h.i * 10 ) + t.i ) * 10 + o.i
0
 
XGISAuthor Commented:
Hello

Thanks for your prompt response; the script you provided when saving falls over on the temp tables #digit and will not save as a view.

Effectively i need either advise as to the best method to achieve my goals or a solution to create either a sp that can be called in a view, or a script that can be saved as a view.

create table dbo.Number(    
	n int constraint pk_Number primary key clustered 
	)
;

if object_id( N'tempdb..#digit', N'U' ) is not null 
	drop table #digit;
	
create table #digit(
	i int
	)

insert #digit( i ) values ( 0 ), ( 1 ), ( 2 ), ( 3 ), ( 4 ), ( 5 ), ( 6 ), ( 7 ), ( 8 ), ( 9 )
	
insert dbo.Number( n )
	select
		(( h.i * 10 ) + t.i ) * 10 + o.i
	from #digit o
	cross join #digit t
	cross join #digit h
;



SELECT dbo.Licence.LicencingID, dbo.Licence.PropertyID, dbo.Licence.TAG,
       LTRIM(RTRIM(SUBSTRING( dbo.Licence.TAG, n, 
       CHARINDEX( ',', dbo.Licence.TAG + ',', n ) - n ))) as Value
  FROM dbo.Licence
INNER JOIN Number
    ON SUBSTRING( ',' + dbo.Licence.TAG, n, 1 ) = ',' 
where n <= Len(dbo.Licence.TAG) + 1

	drop table #digit; 
	drop table number; 
/*select count(distinct n) from Number;*/

Open in new window

0
 
PortletPaulfreelancerCommented:
how many numbers do you actually need?

e.g. run

select max(len(dbo.Licence.TAG)) from dbo.Licence
a simple reworking of this is to use CTE's, back soon

but the number you really need would be helpful.
0
 
XGISAuthor Commented:
Hello yes I can retain the [Numbers] table.
0
 
XGISAuthor Commented:
Hello PortletPaul, in keeping the [Numbers] table I am able to save the view. and by using the following syntax my goal has been met, thanks

SELECT        Value
FROM            (SELECT        SUBSTRING(dbo.Licence.TAG, dbo.Numbers.Number, CHARINDEX(',', dbo.Licence.TAG + ',', dbo.Numbers.Number) - dbo.Numbers.Number) 
                                                    AS Value
                          FROM            dbo.Licence INNER JOIN
                                                    dbo.Numbers ON SUBSTRING(',' + dbo.Licence.TAG, dbo.Numbers.Number, 1) = ',' AND LEN(dbo.Licence.TAG) + 1 >= dbo.Numbers.Number) AS X

Open in new window

0
 
XGISAuthor Commented:
quick response, helped me work out the problem. fantastic :D
0
 
PortletPaulfreelancerCommented:
Great retaining the Number table simplifies things.
Divide the code such as this:
CREATE TABLE dbo.Number
        (
                n int CONSTRAINT pk_Number PRIMARY KEY CLUSTERED
        )
;

IF OBJECT_ID(N'tempdb..#digit', N'U') IS NOT NULL
        DROP TABLE #digit;

CREATE TABLE #digit
        (
                i int
        )

INSERT #digit
(
        i)
        VALUES
                ( 0 ), ( 1 ), ( 2 ), ( 3 ), ( 4 ), ( 5 ), ( 6 ), ( 7 ), ( 8 ), ( 9 )

INSERT dbo.Number
(
        n)
        SELECT
                ((h.i * 10) + t.i) * 10 + o.i
        FROM #digit o
        CROSS JOIN #digit t
        CROSS JOIN #digit h
;

DROP TABLE #digit;

Open in new window

then for your view:
CREATE VIEW whatever_v -- choose appropriate name
AS

SELECT
        dbo.Licence.LicencingID
      , dbo.Licence.PropertyID
      , dbo.Licence.TAG
      , LTRIM(RTRIM(SUBSTRING(dbo.Licence.TAG, dbo.Number.n,
        CHARINDEX(',', dbo.Licence.TAG + ',', dbo.Number.n) - dbo.Number.n))) AS Value
FROM dbo.Licence
INNER JOIN dbo.Number
        ON SUBSTRING(',' + dbo.Licence.TAG, dbo.Number.n, 1) = ','
WHERE n <= LEN(dbo.Licence.TAG) + 1

Open in new window

0
 
PortletPaulfreelancerCommented:
Cheers & Thanks, Paul
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.