SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Split column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

folumike
Starting Member

24 Posts

Posted - 05/16/2013 :  23:54:37  Show Profile  Reply with Quote
I have a table like this:
CREATE TABLE [dbo].[tblcustomer](
[custid] [nvarchar](50) NULL,
[date1] [datetime] NULL,
[Quantity] [float](8) NULL,
[invoicenum] [nvarchar](50) NULL,
[price] [money] NULL

) ON [PRIMARY]

INSERT tblcustomer (custid, date1, Quantity,invnum, price)
VALUES ('0001', '01/01/2013', 60,'INV001/INV002/INV003/INV004/INV005', 30,20,40,45,76)
VALUES ('0002', '01/01/2013', 50, 'INV001', 50)
VALUES ('0001', '01/01/2013', 30, '', )
VALUES ('0001', '02/01/2013', 70, '', )
VALUES ('0002', '01/01/2013', 74, 'INV020/INV021', 15,30)

How do I split the table with columns (invnum and price) that have delimeters '/' and ','.
Note that some of the two columns are empty and some have no delimeters.


Expected OUTPUT
custid | date1 | Quantity |invnum | price

tblcustomer

Please help...

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 05/17/2013 :  00:14:11  Show Profile  Reply with Quote
Use User-Defined function to split CSV Data
-- UDF 
CREATE FUNCTION [dbo].[CustomSplit] (@sep char(1), @s varchar(4000))
RETURNS table
AS
RETURN (
WITH Pieces(n, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT n + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT n,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END) AS Val
FROM Pieces
)
GO

SELECT custid, date1, Quantity, a.Val AS Invoice, b.Val as Price 
FROM tblcustomer
CROSS APPLY CustomSplit('/', invoicenum)a
CROSS APPLY CustomSplit(',', price)b


--
Chandu
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 05/17/2013 :  00:34:00  Show Profile  Reply with Quote
Hi,
The above T-SQL is compatible with 2005 onwards...
You can refer the following link for Split functionality in MSSQL 2000
http://www.codeproject.com/Questions/526739/ConvertplusColumnplusdataplusintoplusRowsplusthrou

--
Chandu
Go to Top of Page

noblemfd
Starting Member

Nigeria
36 Posts

Posted - 05/17/2013 :  00:54:25  Show Profile  Reply with Quote
quote:
Originally posted by bandi

Use User-Defined function to split CSV Data
-- UDF 
CREATE FUNCTION [dbo].[CustomSplit] (@sep char(1), @s varchar(4000))
RETURNS table
AS
RETURN (
WITH Pieces(n, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT n + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT n,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END) AS Val
FROM Pieces
)
GO

SELECT custid, date1, Quantity, a.Val AS Invoice, b.Val as Price 
FROM tblcustomer
CROSS APPLY CustomSplit('/', invoicenum)a
CROSS APPLY CustomSplit(',', price)b


--
Chandu



I checked the site you directed me to, but could not understand it. Please kindly convert the one one you wrote with MSSQL 2005 to MSSQL 2000. THNAKS
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 05/17/2013 :  01:53:42  Show Profile  Reply with Quote
--May be this? I have no MSSQL 2000

SELECT Temp1.custid
	,Temp1.date1
	,Temp1.Quantity
	,Temp1.Invoices
	,temp2.Prices
FROM 
(SELECT  a.Custid,	a.date1, a.Quantity
	,SUBSTRING('/' + a.invoicenum + '/', n.Number + 1, CHARINDEX('/', '/' + a.invoicenum  + '/', n.Number + 1) - n.Number - 1) AS [Invoices]
	FROM tblcustomer AS a
	INNER JOIN master..spt_values AS n ON SUBSTRING('/' + a.invoicenum  + '/', n.Number, 1) = '/' 
	WHERE n.Type = 'p' AND n.Number > 0 AND n.Number < LEN('/' + a.invoicenum  + '/') 
)Temp1
JOIN (SELECT  a.Custid, a.date1, a.Quantity, 
		SUBSTRING(',' + a.price + ',', n.Number + 1, CHARINDEX(',', ',' + a.price  + ',', n.Number + 1) - n.Number - 1) AS [Prices]
		FROM tblcustomer AS a
		INNER JOIN master..spt_values AS n ON SUBSTRING(',' + a.price + ',', n.Number, 1) = ',' 
		WHERE n.Type = 'p' AND n.Number > 0 AND n.Number < LEN(',' + a.price  + ',') 
)temp2
ON temp2.custid = temp1.custid AND temp2.date1 = Temp1.date1 AND temp2.Quantity = Temp1.Quantity


--
Chandu
Go to Top of Page

folumike
Starting Member

24 Posts

Posted - 05/21/2013 :  14:39:10  Show Profile  Reply with Quote
MS SQL 2000

Thanks very it works. But the problem is that it duplicates the values of Invoices and Prices, that is those ones it splits.
For example if the input is
custid | date1 | Quantity |invnum | price
0001 |01/01/2013 |100 | INV001/INV002 | 30,40

It gives:
custid | date1 | Quantity |invnum | price
0001 |01/01/2013 |100 | INV001 | 30
0001 |01/01/2013 |100 | INV001 | 40
0001 |01/01/2013 |100 | INV002 | 30
0001 |01/01/2013 |100 | INV002 | 40

Instead of:

custid | date1 | Quantity |invnum | price
0001 |01/01/2013 |100 | INV001 | 30
0001 |01/01/2013 |100 | INV002 | 40

Please kindly help


quote:
Originally posted by bandi

--May be this? I have no MSSQL 2000

SELECT Temp1.custid
	,Temp1.date1
	,Temp1.Quantity
	,Temp1.Invoices
	,temp2.Prices
FROM 
(SELECT  a.Custid,	a.date1, a.Quantity
	,SUBSTRING('/' + a.invoicenum + '/', n.Number + 1, CHARINDEX('/', '/' + a.invoicenum  + '/', n.Number + 1) - n.Number - 1) AS [Invoices]
	FROM tblcustomer AS a
	INNER JOIN master..spt_values AS n ON SUBSTRING('/' + a.invoicenum  + '/', n.Number, 1) = '/' 
	WHERE n.Type = 'p' AND n.Number > 0 AND n.Number < LEN('/' + a.invoicenum  + '/') 
)Temp1
JOIN (SELECT  a.Custid, a.date1, a.Quantity, 
		SUBSTRING(',' + a.price + ',', n.Number + 1, CHARINDEX(',', ',' + a.price  + ',', n.Number + 1) - n.Number - 1) AS [Prices]
		FROM tblcustomer AS a
		INNER JOIN master..spt_values AS n ON SUBSTRING(',' + a.price + ',', n.Number, 1) = ',' 
		WHERE n.Type = 'p' AND n.Number > 0 AND n.Number < LEN(',' + a.price  + ',') 
)temp2
ON temp2.custid = temp1.custid AND temp2.date1 = Temp1.date1 AND temp2.Quantity = Temp1.Quantity


--
Chandu


Edited by - folumike on 05/21/2013 14:55:06
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/21/2013 :  15:38:33  Show Profile  Reply with Quote
Try 'DISTINCT' as shown in red below:

quote:
Originally posted by folumike

MS SQL 2000

Thanks very it works. But the problem is that it duplicates the values of Invoices and Prices, that is those ones it splits.
For example if the input is
custid | date1 | Quantity |invnum | price
0001 |01/01/2013 |100 | INV001/INV002 | 30,40

It gives:
custid | date1 | Quantity |invnum | price
0001 |01/01/2013 |100 | INV001 | 30
0001 |01/01/2013 |100 | INV001 | 40
0001 |01/01/2013 |100 | INV002 | 30
0001 |01/01/2013 |100 | INV002 | 40

Instead of:

custid | date1 | Quantity |invnum | price
0001 |01/01/2013 |100 | INV001 | 30
0001 |01/01/2013 |100 | INV002 | 40

Please kindly help


quote:
Originally posted by bandi

--May be this? I have no MSSQL 2000

SELECT DISTINCT Temp1.custid
	,Temp1.date1
	,Temp1.Quantity
	,Temp1.Invoices
	,temp2.Prices
FROM 
(SELECT  a.Custid,	a.date1, a.Quantity
	,SUBSTRING('/' + a.invoicenum + '/', n.Number + 1, CHARINDEX('/', '/' + a.invoicenum  + '/', n.Number + 1) - n.Number - 1) AS [Invoices]
	FROM tblcustomer AS a
	INNER JOIN master..spt_values AS n ON SUBSTRING('/' + a.invoicenum  + '/', n.Number, 1) = '/' 
	WHERE n.Type = 'p' AND n.Number > 0 AND n.Number < LEN('/' + a.invoicenum  + '/') 
)Temp1
JOIN (SELECT  a.Custid, a.date1, a.Quantity, 
		SUBSTRING(',' + a.price + ',', n.Number + 1, CHARINDEX(',', ',' + a.price  + ',', n.Number + 1) - n.Number - 1) AS [Prices]
		FROM tblcustomer AS a
		INNER JOIN master..spt_values AS n ON SUBSTRING(',' + a.price + ',', n.Number, 1) = ',' 
		WHERE n.Type = 'p' AND n.Number > 0 AND n.Number < LEN(',' + a.price  + ',') 
)temp2
ON temp2.custid = temp1.custid AND temp2.date1 = Temp1.date1 AND temp2.Quantity = Temp1.Quantity


--
Chandu




Edited by - MuMu88 on 05/21/2013 15:38:51
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000