USE [myActions]
GO

/****** Object:  UserDefinedFunction [dbo].[udf_Table_LineNum]    Script Date: 02/07/2012 17:06:38 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_Table_LineNum]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[udf_Table_LineNum]
GO


/****** Object:  UserDefinedFunction [dbo].[udf_Table_LineNum]    Script Date: 02/07/2012 17:01:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].[udf_Table_LineNum](@ParmList varchar(8000), @Delim varchar(20))
RETURNS @table TABLE 
(LineNum int, Parameter varchar(255))

AS 

/*
 SELECT * FROM dbo.udf_Table_LineNum( 'a|~|b|~|c', '|~|')
 
 SELECT * FROM dbo.udf_Table_LineNum( 'a', '|~|')
 
*/

BEGIN
	DECLARE @x int, @Parameter varchar(255)
	SET @x = 1

	WHILE CHARINDEX(@Delim, @ParmList)-1 > 0
	  BEGIN 
		INSERT INTO @table(LineNum, Parameter) 
		SELECT @x, SUBSTRING(@ParmList,1,CHARINDEX(@Delim, @ParmList)-1)
		

		SELECT @ParmList  = SUBSTRING(@ParmList,CHARINDEX(@Delim, @ParmList)+LEN(@Delim), LEN(@ParmList)-CHARINDEX(@Delim,@ParmList))
		SELECT @x = @x + 1
	  END 
	INSERT INTO @table(LineNum, Parameter)  SELECT @x, @ParmList
	RETURN
END


GO

quote:

---

Originally posted by noblemfd

I WANT TO USE MSSQL 2000 USER-DEFINED FUNCTION TO ACHIEVE THE GOAL BELOW. PLEASE HELP ME OUR, OR IS THERE ANY OTHER METHOD USING MSSQL 2000.

PRESENT STATE
INVNUM RECEIPTNUM DATE AMOUNT

1 000001/000002 11/02/2010 12,222.00/32,234.00
2
3 000003 21/09/2011 32,345.00


EXPECTED RESULT
INVNUM RECEIPTNUM DATE AMOUNT

1 000001 11/02/2010 12,222.00
1 000002 11/02/2010 32,234.00
2
3 000003 21/09/2011 32,345.00

Please kindly assist. Or do you know how I can use MSSQL FUNCTION to resolve it? I need it urgently.
Thanks

---

quote:

---

Originally posted by X002548

How many "/" can INVNUM or AMOUNT have?

The REAL Problem is how do you know o "tie" The data together?

Just because the 1st occurence all belong together?

SELECT *
FROM yourTable D
JOIN (SELECT DATE, dbo.udf_Table_LineNum( INVNUM , '/') FROM yourTable) AS I
ON D.DATE = I.DATE
JOIN (SELECT DATE, dbo.udf_Table_LineNum( AMOUNT , '/') FROM yourTable) AS A
ON D.DATE = A.DATE

This isn't correct..but's in the right direction
You really need a key

Play with this udf

USE [myActions]
GO

/****** Object:  UserDefinedFunction [dbo].[udf_Table_LineNum]    Script Date: 02/07/2012 17:06:38 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_Table_LineNum]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[udf_Table_LineNum]
GO


/****** Object:  UserDefinedFunction [dbo].[udf_Table_LineNum]    Script Date: 02/07/2012 17:01:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].[udf_Table_LineNum](@ParmList varchar(8000), @Delim varchar(20))
RETURNS @table TABLE 
(LineNum int, Parameter varchar(255))

AS 

/*
 SELECT * FROM dbo.udf_Table_LineNum( 'a|~|b|~|c', '|~|')
 
 SELECT * FROM dbo.udf_Table_LineNum( 'a', '|~|')
 
*/

BEGIN
	DECLARE @x int, @Parameter varchar(255)
	SET @x = 1

	WHILE CHARINDEX(@Delim, @ParmList)-1 > 0
	  BEGIN 
		INSERT INTO @table(LineNum, Parameter) 
		SELECT @x, SUBSTRING(@ParmList,1,CHARINDEX(@Delim, @ParmList)-1)
		

		SELECT @ParmList  = SUBSTRING(@ParmList,CHARINDEX(@Delim, @ParmList)+LEN(@Delim), LEN(@ParmList)-CHARINDEX(@Delim,@ParmList))
		SELECT @x = @x + 1
	  END 
	INSERT INTO @table(LineNum, Parameter)  SELECT @x, @ParmList
	RETURN
END


GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

---