Last post Jun 14, 2013 08:47 AM by BharatRam
May 10, 2013 06:27 AM|pravesh.dobhal|LINK
How to split comma separated string into an array which is passed to an oracle function as a parameter and then iterating
through every element of array.....
May 10, 2013 07:05 AM|Rab Nawaz Khan|LINK
I think you want to split the string in C# or vb.net. if i am not wrong then you can use
System.Text.RegularExpressions.Regex class, it contains the Split(string, string) method and return the Array of string based on your split criteria. Please have a look below URL
May 10, 2013 07:08 AM|pravesh.dobhal|LINK
sorry dear...but i want all that through oracle function only....
Jun 14, 2013 08:47 AM|BharatRam|LINK
Hi, I have a solution for this in SQL and it is based on tables. The logic below places the Comma separated strings in a table variable with an integer identifier assigned to each string in Sql. The end result of the below
Sql function would be a table variable with 2 Columns [Id] and [Word] where 'Word' holds the CSV (Comma Separated Values (strings)) and the 'Id' is the identifier for them.
CREATE FUNCTION dbo.Fn_sql_sentence_split (@sentence VARCHAR(200))
returns @splitTable TABLE(
[Id] INT IDENTITY(1, 1),
27072011 Splits a set of words and places them as rows in a table;
As of now the Splitter is defined as an empty space by default;
and its size is restricted to one byte
07092011 Changed Logic for last word Insertion
DECLARE @bufferSentence VARCHAR(200)
DECLARE @splitter CHAR(1)
DECLARE @word VARCHAR(100)
DECLARE @position INT
DECLARE @i INT
SET @bufferSentence = @sentence
SET @splitter = ','
SET @position = 2
--@position set to 2 to make while loop function
WHILE( @position > 1 )
--the position of the splitter is found so that we can separate the word.
SET @position = Charindex(@splitter, @bufferSentence)
--the word which is to be inserted to the temp table is obtained
SET @word = Substring(@bufferSentence, 0, @position)
IF( Len(@word) > 0 )
--the parent word is chopped down for further extraction
--SET @bufferSentence = Ltrim(Substring(@bufferSentence, @position,(Len(@bufferSentence) - @position ) + 1))
SET @bufferSentence = Ltrim(Substring(@bufferSentence, @position + 1
Len(@bufferSentence) - @position ) + 1))
--to insert the final word
/*DECLARE @splitterLength INT
SET @splitterLength = LEN(@splitter)
IF(@splitter <> ' ' AND @splitter <> '')
SET @bufferSentence = SUBSTRING(@bufferSentence,@splitterLength+1,LEN(@bufferSentence))*/
IF( Len(@bufferSentence) > 0 )
Once you have it in a table you can iterate that using a Cursor or by ordinary looping.
But my solution is in Sql which you have to convert to Oracle.
To execute this use the syntax below: SELECT * FROM dbo.fn_SQL_SENTENCE_SPLIT('This is for, test') SELECT * FROM dbo.fn_SQL_SENTENCE_SPLIT('This,is,for , test') If you want to store values permanently then use a Table instead of a Table variable.