October 25th, 2004, 07:34 PM
|
|
Contributing User
|
|
Join Date: May 2003
Location: Brisbane, Australia
|
|
How can I create a function?
I'm trying to create a function that converts a delimited string to a table. I've got code but I can't make it run. I've tried running it in toad, from sqlplus, from enterprise manager/SQL Scratch Pad. They all fail! I can't even make it try and run from sqlplus, it justs gets stuck in that command listing mode (you know where it lists numbers down the left hand side that increment each time you hit enter) and nothing I do will make it exit. (Except for Ctrl-C, hardly useful).
Can you give me any ideas as to how I can run this code?
Code:
CREATE OR REPLACE FUNCTION convert_string
(
@PInStrSource varchar(8000) = NULL,
@pInChrSeparator char(1) = ','
)
RETURNS
@ARRAY TABLE (ItemValue VARCHAR(1000))
AS
BEGIN
DECLARE @CurrentStr varchar(2000)
DECLARE @ItemStr varchar(200)
SET @CurrentStr = @PInStrSource
WHILE Datalength(@CurrentStr) > 0
BEGIN
IF CHARINDEX(@pInChrSeparator, @CurrentStr,1) > 0
BEGIN
SET @ItemStr = SUBSTRING (@CurrentStr, 1, CHARINDEX(@pInChrSeparator, @CurrentStr,1) - 1)
SET @CurrentStr = SUBSTRING (@CurrentStr, CHARINDEX(@pInChrSeparator, @CurrentStr,1) + 1, (Datalength(@CurrentStr) - CHARINDEX(@pInChrSeparator, @CurrentStr,1) + 1))
INSERT @ARRAY (ItemValue) VALUES (@ItemStr)
END
ELSE
BEGIN
INSERT @ARRAY (ItemValue) VALUES (@CurrentStr)
BREAK;
END
END
RETURN
END
__________________
Like the answers I give? Why not ask me directly at my forum. I'm always glad to help.
Javascript scripts and tips can be found at Dynamic Tools.
Check out DynamicTable, the best javascript table sorter around.
Get reliable and affordable hosting at www.thinksmarthosting.com
|
Dev Shed Forums Sponsor:
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
