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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ArgentiCommented:
you have an extra ")" after IGNORE_DUP_KEY
0
XGISAuthor Commented:
pls note this typo is not in the syntax, thankyou though
0
PortletPaulEE Topic AdvisorCommented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

ArgentiCommented:
To me, after creating the Licence table, it's working just fine.
0
PortletPaulEE Topic AdvisorCommented:
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
PortletPaulEE Topic AdvisorCommented:
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
PortletPaulEE Topic AdvisorCommented:
oh, & Are you able to retain the table [Numbers]? That would save you lots of time/effort.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
PortletPaulEE Topic AdvisorCommented:
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
PortletPaulEE Topic AdvisorCommented:
Cheers & Thanks, Paul
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.