Avatar of XGIS
XGISFlag for Australia asked on

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.
Microsoft SQL ServerMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment
PortletPaul

8/22/2022 - Mon
Argenti

you have an extra ")" after IGNORE_DUP_KEY
ASKER
XGIS

pls note this typo is not in the syntax, thankyou though
PortletPaul

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)
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Argenti

To me, after creating the Licence table, it's working just fine.
PortletPaul

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
ASKER
XGIS

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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
PortletPaul

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.
ASKER CERTIFIED SOLUTION
PortletPaul

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
XGIS

Hello yes I can retain the [Numbers] table.
ASKER
XGIS

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

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
XGIS

quick response, helped me work out the problem. fantastic :D
PortletPaul

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

PortletPaul

Cheers & Thanks, Paul
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.