I have a table and it has a value column that lists data as: Row 1: '00','01','02','03' Row 2: '03','02','09','08'
I have a couple of split functions
FUNCTION [dbo].[udf_Split](@String varchar(MAX), @Delimiter char(1))
returns @temptable TABLE (Item varchar(MAX))
as
begin
declare @idx int
declare @slice varchar(8000)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx<>0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Item) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end return end;
I'm trying to create a view of the table, with that column and then I'd like my view results to be a list of rows that have each value broken to its own row (and distinct) So would look like: (the tics can stay or go, don't care about them right now) Row 1: 00 Row 2: 01 Row 3: 02 Row 4: 03
My view is pretty much a:
SELECT DISTINCT VALUE FROM TABLE
cross apply dbo.split(Value, ',') as Item
But it's not working. Can someone lend me some direction on how I should work this?
Aucun commentaire:
Enregistrer un commentaire