Parse delimited data in column to multiple columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Parse delimited data in column to multiple columns

Author

Topic

ahmeterispaha
Starting Member

19 Posts

Posted - 2008-06-13 : 12:21:07

I'm working on a sales commission report that will show commissions for up to 5 sales reps for each invoice. The invoice detail table contains separate columns for the commission rates payable to each rep, but for some reason the sale srep IDs are combined into one column. The salesrep column may contain null, a single sales rep id, or up to five slaes rep IDs separated by the '~' character.

So I'd like to parse the rep IDs from a single column (salesreplist) in my invoice detail table (below) to multiple columns (RepID1, RepID2, RepID3, RepID4,RepID5) in a temp table so I can more easily calculate the commission amounts for each invoice and sales rep.

Here is my table:

CREATE TABLE invcdtl(
invoicenum int,
salesreplist [text] NULL,
reprate1 int NULL,
reprate2 int NULL,
reprate3 int NULL,
reprate4 int NULL,
reprate5 int NULL,
)

Here is some sample data:

1 A 0 0 0 0 0
2 0 0 0 0 0
3 I~~~~ 15 0 0 0 0
4 A~B 5 5 0 0 0
5 I~F~T~K~G 5 5 2 2 2
6 A~B

As you can see, some records have trailing delimiters but some don't. This may be a result of the application's behavior when multiple reps are entered then removed from an invoice. One thing for sure is that when there are multiple reps, the IDs are always separated by '~'

Can anyone suggest a solution?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-13 : 12:36:33

create a UDF like this

CREATE FUNCTION ParseValues  
(@String varchar(8000)  
)  
RETURNS @RESULTS TABLE  
(ID int identity(1,1),  
Val int  
)  
AS  
BEGIN  
DECLARE @Value varchar(100)  
  
WHILE @String is not null  
BEGIN  
SELECT @Value=CASE WHEN CHARINDEX(',',@String) >0 THEN LEFT(@String,CHARINDEX(',',@String)-1) ELSE @String END,  
@String=CASE WHEN CHARINDEX(',',@String) >0 THEN SUBSTRING(@String,CHARINDEX(',',@String)+1,LEN(@String)) ELSE NULL END  
INSERT INTO @RESULTS (Val)   
SELECT @Value   
END  
RETURN  
END

and use this to parse your values. something like

SELECT i.invoicenum,b.Val
FROM invcdtl i
CROSS APPLY dbo.ParseValues(REPLACE(REPLACE(i.salesreplist,' ',','),'~',','))b

ahmeterispaha
Starting Member

19 Posts

Posted - 2008-06-13 : 13:03:32

Thanks for your quick reply.

I don't have CREATE FUNCTION permission in the database. Is there a way to code this without placing a new object in the database?

ahmeterispaha
Starting Member

19 Posts

Posted - 2008-06-13 : 15:28:07

With a couple of minor changes, I was able to get visakh16's code to run in a test environment:

1. The REPLACE function didn't like text data type for the first argument, so I added a CONVERT, as in:
CROSS APPLY dbo.ParseValues(REPLACE(REPLACE(CONVERT(varchar(50),i.salesreplist),' ',','),'~',','))b

2. Changed the datatype of the second column in the @RESULTS table in the ParseValues UDF from int to varchar(50).

Now that I'm able to run this, I see that it's giving me the sales rep IDs in multiple rows. Does anyone have a suggestion on breaking out the sales rep codes into multiple columns rather than rows? Also, I would still like to explore an alternative solution where I don't have to create a UDF in the database. Any ideas on that?

Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources