Split column - SQL Server Forums

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

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

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

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

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources