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.
Some guidance would be most appreciated.
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
Some guidance would be most appreciated.
Last Comment
Argenti
you have an extra ")" after IGNORE_DUP_KEY
ASKER
pls note this typo is not in the syntax, thankyou though
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.
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;
To me, after creating the Licence table, it's working just fine.
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
it can be pushed up one if needed, amending line 21
1 + (( h.i * 10 ) + t.i ) * 10 + o.i
ASKER
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.
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;*/
how many numbers do you actually need?
e.g. run
but the number you really need would be helpful.
e.g. run
select max(len(dbo.Licence.TAG)) from dbo.Licence
a simple reworking of this is to use CTE's, back soonbut the number you really need would be helpful.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Hello yes I can retain the [Numbers] table.
ASKER
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
ASKER
quick response, helped me work out the problem. fantastic :D
Great retaining the Number table simplifies things.
Divide the code such as this:
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;
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
Cheers & Thanks, Paul
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
