SQL Syntax question

Easiest way would be to split out the code and QTY so it's a 1:1:1 relationship.
Table grows, but tables do that.

IF you wanted to keep all the same:
https://www.experts-exchange.com/questions/24034874/T-SQL-Split-row-on-delimiter.html

What DB Engine and Version?

If it's SQL Server 2005 you can use a UDF to split the records.  Just provide your table names and your existing Query, then I can help you out.

SQL Server 2005 v. 9.000.1399
The code below ran for 15 minutes. Ive used it to parse 1 column, in another scenario, so I tried to use it again but with an extra column 'QTY'. I think Im close but it was taking too long...And GED325 is right I want them all 1:1:1...

Existing code:
insert into GlendaleTest
      select PatAccount, Codes,Qty from GlendaleTemp;
 
     ;WITH CTE (ID, Code, Remain, Level,Qty)
      AS
      (
      -- Anchor member definition
      SELECT PatAccount, Code = Codes, Remain = Codes, Level = 0,Qty
      FROM GlendaleTest
      UNION ALL
      -- Recursive member definition
      SELECT ID,
      Code = left(Remain, isnull(position_of_space, remain_length)),
      Remain = right(Remain, isnull(remain_length - position_of_space, 0)), Level = 1,Qty
            FROM
            (  SELECT
      position_of_space = nullif(PatIndex('%;%', Remain), 0),
      remain_length = len(Remain),
      CTE.ID, CTE.Remain,CTE.Qty
      FROM CTE
      INNER JOIN GlendaleTest on GlendaleTest.PatAccount = CTE.ID
      WHERE Remain <> ''
            ) X
      )
      -- Statement that executes the CTE
      SELECT ID, Code,QTY
      INTO GlendaleStage
      FROM CTE
      WHERE Level = 1
      Order by id

sql 2005 is easy:

Use the cross apply (given in my first link) to get the apprropriate select statement down.    Would then re-insert to a new table and drop the old.

What datatype is PatAccount?

will try...Thanks

PatAccount varchar(50)
Codes varchar(max)
QTY varchar(max)

I think the problem with using Cross apply will be relating the 1,2,3,4,5 CODES to 1,2,3,4,5 QTY.

I was just thinking on how to apply the function to the 2 fields myself? How would the select look? If you guys need data let me know...Thank you both for your help...

This worked for me to seprate out even more columns that were in the same format but I have had trouble getting it to work

ALTER function [dbo].[fn_Split](@ID int) returns @temp table(ID int,CODE varchar(500),QTY varchar(500),AMT varchar(500),CPTCode varchar(500)) as
begin

declare @Code varchar(5000),@Qty varchar(5000),@Amt varchar(5000),@CPTCode varchar(5000)
declare @SubCode varchar(5000),@SubQty varchar(5000),@SubAmt varchar(5000),@SubCPTCode varchar(5000)
declare @string varchar(5000)
declare @CodeTable table(UniqueID int identity,ID int,CODE varchar(500))
declare @QtyTable table(UniqueID int identity,ID int,QTY varchar(500))
declare @AmtTable table(UniqueID int identity,ID int,AMT varchar(500))
declare @CPTCodeTable table(UniqueID int identity,ID int,CPTCode varchar(500))
select @Code  = RevCodes from SacramentoTrans t1 where PatAccount = @ID
select @Qty  = QTY from SacramentoTrans t1 where PatAccount = @ID
select @Amt  = Amount from SacramentoTrans t1 where PatAccount = @ID
select @CPTCode  = CPTCodes from SacramentoTrans t1 where PatAccount = @ID
set @SubCode = @Code+';'
set @SubQty = @Qty+';'
set @SubAmt = @Amt+';'
set @SubCPTCode = @CPTCode+';'

while @SubCode <> '' and len(@SubCode) > 0 and @SubCode IS NOT NULL
begin
set @string = substring(@SubCode,1,charindex(';',@SubCode)-1)
insert @CodeTable values (@ID,@string)
set @SubCode = substring(@SubCode,charindex(';',@SubCode)+1,len(@SubCode))
end
 
while @SubQty <> '' and len(@SubQty) > 0 and @SubQty IS NOT NULL
begin
set @string = substring(@SubQty,1,charindex(';',@SubQty)-1)
insert @QtyTable values (@ID,@string)
set @SubQty = substring(@SubQty,charindex(';',@SubQty)+1,len(@SubQty))
end
 
while @SubAmt <> '' and len(@SubAmt) > 0 and @SubAmt IS NOT NULL
begin
set @string = substring(@SubAmt,1,charindex(';',@SubAmt)-1)
insert @AmtTable values (@ID,@string)
set @SubAmt = substring(@SubAmt,charindex(';',@SubAmt)+1,len(@SubAmt))
end
 
while @SubCPTCode <> '' and len(@SubCPTCode) > 0 and @SubCPTCode IS NOT NULL
begin
set @string = substring(@SubCPTCode,1,charindex(';',@SubCPTCode)-1)
insert @CPTCodeTable values (@ID,@string)
set @SubCPTCode = substring(@SubCPTCode,charindex(';',@SubCPTCode)+1,len(@SubCPTCode))
end
 
insert into @temp
select coalesce(t5.id,t6.id) ID ,CODE,QTY,AMT,CPTCode
 from (
select coalesce(t3.id,t4.id) ID ,coalesce(t3.UniqueID,t4.UniqueID) UniqueID,CODE,QTY,AMT
  from(
select coalesce(t1.id,t2.id) ID ,coalesce(t1.UniqueID,t2.UniqueID) UniqueID,CODE,QTY
 from @CodeTable t1 full join @QtyTable t2 on t1.ID = t2.ID and t1.UniqueID = t2.UniqueID) t3
 full join @AmtTable t4 on t3.ID = t4.ID and t3.UniqueID = t4.UniqueID) t5
 full join @CPTCodeTable t6 on t5.ID = t6.ID and t5.UniqueID = t6.UniqueID return
end

Brandon: I ran your query using the function and I got the same data that I had, in the same format. I created the view as well.....

Hey guys I did it with this....Thanks for all your help...Split the points?

declare @Code varchar(5000),@Qty varchar(5000)
declare @SubCode varchar(5000),@SubQty varchar(5000)
declare @string varchar(5000)
declare @CodeTable table(UniqueID int identity,ID varchar(50),CODE varchar(500))
declare @QtyTable table(UniqueID int identity,ID varchar(50),QTY varchar(500))
select @Code  = Codes from GlendaleTempRev t1 where PatAccount = @ID
select @Qty  = QTY from GlendaleTempRev t1 where PatAccount = @ID
set @SubCode = @Code+';'
set @SubQty = @Qty+';'

while @SubCode <> '' and len(@SubCode) > 0 and @SubCode IS NOT NULL
begin
set @string = substring(@SubCode,1,charindex(';',@SubCode)-1)
insert @CodeTable values (@ID,@string)
set @SubCode = substring(@SubCode,charindex(';',@SubCode)+1,len(@SubCode))
end
 
while @SubQty <> '' and len(@SubQty) > 0 and @SubQty IS NOT NULL
begin
set @string = substring(@SubQty,1,charindex(';',@SubQty)-1)
insert @QtyTable values (@ID,@string)
set @SubQty = substring(@SubQty,charindex(';',@SubQty)+1,len(@SubQty))
end
 
insert into @temp
select coalesce(t1.id,t2.id) ID ,CODE,QTY
 from @CodeTable t1 full join @QtyTable t2 on t1.ID = t2.ID and t1.UniqueID = t2.UniqueID
return

Same results, sure.  Because you said what you wanted.  But was it faster?

I didnt need the same results. I needed the results parsed out. Miscommunication

it should have shown you qty and code (your original columns) as well as the parsed qty/code values.  Are you SURE you have the SQL as below?

Select PatAccount, qty,code, fn.qty,fn.code
from GlendateTest gt
cross apply [dbo].[fn_DelimitedToTable_Qty_Code] (gt.pataccount) fn

Yeah and it showed my table columns and columns using the function exactly the same.

I found a typo....


set @Delimiter=N'n'

should be:

set @Delimiter=N','

if object_id('[dbo].[fn_DelimitedToTable_Qty_Code]') is not null
     drop function [dbo].[fn_DelimitedToTable_Qty_Code]
go
create function [dbo].[fn_DelimitedToTable_Qty_Code](@PatAccount varchar(50))
returns @Values TABLE
     (ident         int not null identity primary key clustered
     ,thePosition   int not null
     ,Qty           nvarchar(max)
     ,Code          nvarchar(max)
     )
as
/************************************************************
*
*    Author:        Brandon Galderisi
*    Last modified: 07-Oct-2008
*
*************************************************************/
begin
     declare @Delimiter  nchar(1)
          ,@Qty          nvarchar(max)
          ,@Codes        nvarchar(max)
 
     select @Qty = qty, @Codes = Codes
     from GlendaleTest
     where PatAccount = @PatAccount
 
declare @Values2 TABLE
     (ident         int not null identity primary key clustered
     ,thePosition   int not null
     ,Qty           nvarchar(max)
     ,Code          nvarchar(max)
     )
 
     set @Delimiter=N','
     insert into @Values2 (thePosition,Qty)
                select vn.n, substring(@delimiter + @Qty + @delimiter, vn.n + (datalength(@delimiter)/2), charindex(@delimiter, @delimiter + @Qty + @delimiter, vn.n + datalength(@delimiter)/2) - vn.n - datalength(@delimiter)/2) as string_value
                from    dbo.vw_Nums vn
                where
                        vn.n <= (datalength(@delimiter + @Qty + @delimiter)/2) - (datalength(@delimiter)/2)
                        and substring(@delimiter + @Qty + @delimiter, vn.n, (datalength(@delimiter)/2)) = @delimiter
 
     insert into @Values2 (thePosition,Codes)
        select vn.n, substring(@delimiter + @Codes + @delimiter, vn.n + (datalength(@delimiter)/2), charindex(@delimiter, @delimiter + @Codes + @delimiter, vn.n + datalength(@delimiter)/2) - vn.n - datalength(@delimiter)/2) as string_value
        from    dbo.vw_Nums vn
        where
                vn.n <= (datalength(@delimiter + @Codes + @delimiter)/2) - (datalength(@delimiter)/2)
                and substring(@delimiter + @Codes + @delimiter, vn.n, (datalength(@delimiter)/2)) = @delimiter
 
     insert into @Values (thePosition,Qty,Code)
     select a.ThePosition, a.Qty,b.Code
     from @Values2 a
       join @Values2 b
         on a.theposition=b.thePosition
         and a.Qty is not null
         and b.Code is not null
return
end
/*
-- The purpose of vw_Nums is if the source database does not have a numbers table.  This view (vw_nums)
-- will allow the parsing of up to 4 billion+ character strings with the above function.  Whether a static
-- table or this view is used for fn_DelimitedToTable, it can only split a string as long as the source 
-- numbers table.
Requires a nunbers table or this view:
create view vw_Nums
as
with   cte0 as (select 1 as c union all select 1), 
       cte1 as (select 1 as c from cte0 a, cte0 b), 
       cte2 as (select 1 as c from cte1 a, cte1 b), 
       cte3 as (select 1 as c from cte2 a, cte2 b), 
       cte4 as (select 1 as c from cte3 a, cte3 b), 
       cte5 as (select 1 as c from cte4 a, cte4 b), 
       nums as (select row_number() over (order by c) as n from cte5)
       select n from nums 
 
 
 
Sample Usage:
select * from [dbo].[fn_DelimitedToTable_Qty_Code]('a|%25basdf|%25c|%25d','|%25')
select theValue from [dbo].[fn_DelimitedToTable_Qty_Code]('a','|')
select * from [dbo].[fn_DelimitedToTable_Qty_Code]('a basdf c d',' ')
*/
GO

Select allOpen in new window

No prob man, keep checkin for me because I always got questions and I will throw the points to you for your help..Thanks again..

I think you should have it re-opened and accept the updated function with a better grade.  A typo doesn't warrant a "B" and you didn't exactly give me an opportunity to fix it :).

No problem, I got no problem with that. Just dont know how

"request Attention" via the link in your original post.  Just ask for it to be reopened.

Thanks Brandon

Thank you!!