• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 237
  • Last Modified:

SQL Syntax question

I have posted something similiar to this before. I have 1 row with an AccountID, Code and QTY. The accountid only has one value but the code and qty columns contain x number of values ';' delimited. I need the accountid, codes and qty columns parsed out for each one of the codes and qty columns into there own rows. Is there an easier way? I am trying to stay away from cursors but whatever will work I am willing to use. Please help!!
0
healthcheckinc
Asked:
healthcheckinc
  • 13
  • 9
  • 2
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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:
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_24034874.html
0
 
BrandonGalderisiCommented:
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.
0
 
healthcheckincAuthor Commented:
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
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Kyle AbrahamsSenior .Net DeveloperCommented:
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.
0
 
BrandonGalderisiCommented:
What datatype is PatAccount?
0
 
healthcheckincAuthor Commented:
will try...Thanks
0
 
healthcheckincAuthor Commented:
PatAccount varchar(50)
Codes varchar(max)
QTY varchar(max)
0
 
BrandonGalderisiCommented:
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.
0
 
healthcheckincAuthor Commented:
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...
0
 
healthcheckincAuthor Commented:
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







0
 
BrandonGalderisiCommented:
Let's try this.

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

Using this function.
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'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

Open in new window

0
 
healthcheckincAuthor Commented:
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.....
0
 
healthcheckincAuthor Commented:
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
0
 
BrandonGalderisiCommented:
Same results, sure.  Because you said what you wanted.  But was it faster?
0
 
healthcheckincAuthor Commented:
I didnt need the same results. I needed the results parsed out. Miscommunication
0
 
BrandonGalderisiCommented:
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

0
 
healthcheckincAuthor Commented:
Yeah and it showed my table columns and columns using the function exactly the same.
0
 
BrandonGalderisiCommented:
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

Open in new window

0
 
healthcheckincAuthor Commented:
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..
0
 
BrandonGalderisiCommented:
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 :).
0
 
healthcheckincAuthor Commented:
No problem, I got no problem with that. Just dont know how
0
 
BrandonGalderisiCommented:
"request Attention" via the link in your original post.  Just ask for it to be reopened.
0
 
healthcheckincAuthor Commented:
Thanks Brandon
0
 
healthcheckincAuthor Commented:
Thank you!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 13
  • 9
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now