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 2000 Forums
 SQL Server Development (2000)
 Split column

Author  Topic 

folumike
Starting Member

24 Posts

Posted - 2013-05-16 : 23:54:37
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-17 : 00:14:11
[code]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
[/code]

--
Chandu
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-17 : 00:34:00
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

38 Posts

Posted - 2013-05-17 : 00:54:25
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-17 : 01:53:42
--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 - 2013-05-21 : 14:39:10
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

Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-21 : 15:38:33
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



Go to Top of Page
   

- Advertisement -