firebird.split2int.plsql

set term ^ ;

create or alter procedure split2int (
	input_string varchar(8192)),
	delimitet varchar(6) = ',')
returns (
	column_value integer)
as
declare variable this_pos integer;
declare variable next_pos integer;
begin
	delimiter = coalesce(nullif(delimiter, ''), ',');
	this_pos = 1;
	next_pos = position(delimiter, input_string, this_pos);
	while(next_pos > 0) do begin
		column_value = cast(substring(input_string from this_pos for next_pos - this_pos) as int);
		suspend;
		this_pos = next_pos + 1;
		next_pos = position(delimiter, input_string, this_pos);
	end
	column_value = cast(substring(input_string from this_pos) as int);
	suspend;
end^

set term ; ^

firebird.split2int_safe.plsql

set term ^ ;

create or alter procedure split2int_safe (
	input_string varchar(8192),
	delim varchar(6) = ',')
returns (
	column_value integer)
as
declare variable this_pos integer;
declare variable next_pos integer;
begin
	this_pos = 1;
	delim = coalesce(nullif(delim, ''), ',');
	next_pos = position(delim, input_string, this_pos);
	while(next_pos > 0) do begin
		if(substring(
			input_string from this_pos for next_pos - this_pos
		) similar to '[[:WHITESPACE:]]*[[:DIGIT:]]+[[:WHITESPACE:]]*') then begin
			column_value = cast(substring(input_string from this_pos for next_pos - this_pos) as int);
			suspend;
		end
		this_pos = next_pos + 1;
		next_pos = position(delim, input_string, this_pos);
	end
	if(substring(
		input_string from this_pos
	) similar to '[[:WHITESPACE:]]*[[:DIGIT:]]+[[:WHITESPACE:]]*') then begin
		column_value = cast(substring(input_string from this_pos) as int);
		suspend;
	end
end^

set term ; ^

firebird.sql

with recursive input_strings as (
	select
		cast(:input_string as varchar(1024)) as mystring
	from rdb$database
), input_data as (
	select
		position(',', mystring) + 1 as pos,
		(char_length(mystring) - position(',', reverse(mystring))) + 1 as lim,
		cast(
			substring(
				mystring from 1 for (position(',', mystring, 1) - 1)
			) as int
		) as column_value
	from input_strings
	where
		position(',', mystring) > 0
	union all
	select
		position(',', mystring, pos) + 1,
		lim,
		cast(
			substring(
				mystring from pos for (
					position(',', mystring, pos + 1) - pos
				)
			) as int
		) as column_value
	from input_data, input_strings
	where
		pos between 1 and lim
)
select column_value from input_data
union all
select
	cast(case position(',', mystring)
		when 0 then mystring
		else substring(mystring from
			(char_length(mystring) - position(',', reverse(mystring))) + 2
		)
	end as int)
from input_strings

mssql2012.sql

with input_data as (
	select
		charindex(',', @input_string) + 1 as pos,
		(len(@input_string) - charindex(',', reverse(@input_string))) + 1 as lim,
		cast(
			substring(
				@input_string,
				1,
				charindex(',', @input_string, 1) - 1
			) as int
		) as column_value
	where
		charindex(',', @input_string) > 0
	union all
	select
		charindex(',', @input_string, pos) + 1,
		lim,
		cast(
			substring(
				@input_string,
				pos,
				charindex(',', @input_string, pos + 1) - pos
			) as int
		) as column_value
	from input_data
	where
		pos between 1 and lim
)
select column_value from input_data
union all
select
	cast(case charindex(',', @input_string)
		when 0 then @input_string
		else substring(
			@input_string,
			(len(@input_string) - charindex(',', reverse(@input_string))) + 2,
			len(@input_string)
		)
	end as int)

mysql.plsql

CREATE DEFINER=`root`@`%` PROCEDURE `split2int`(
	in input_string varchar(8192),
	in delim varchar(6)
)
BEGIN
	declare this_pos integer;
	declare next_pos integer;
	drop temporary table if exists split2int_temptab;
	create temporary table split2int_temptab (column_value integer);
	set this_pos = 1;
	set next_pos = locate(delim, input_string, this_pos);
	while(next_pos > 0) do
		insert into split2int_temptab select
			cast(substring(input_string, this_pos, next_pos - this_pos) as signed) as column_value;
		set this_pos = next_pos + 1;
		set next_pos = locate(delim, input_string, this_pos);
	end while;
	insert into split2int_temptab select
		cast(substring(input_string, this_pos) as signed) as column_value;
	select column_value from split2int_temptab;
	drop temporary table split2int_temptab;
END

oracle10.sql

select
	cast(regexp_substr(:input_string,'[^,]+', 1, level) as int) column_value
from dual
connect by
	regexp_substr(:input_string, '[^,]+', 1, level) is not null

oracle9.sql

select /*+ cardinality(dual, 1)*/
	cast(
		coalesce(
			substr(
				:input_string,
				(case
					when level = 1
					then 1
					else instr(:input_string, ',', 1, level - 1) + 1
				end),
				(case
					when level = 1
					then instr(:input_string, ',') - 1
					else instr(:input_string, ',', 1, level) - instr(:input_string, ',', 1, level - 1) - 1
				end)
			),
			substr(:input_string, instr(:input_string, ',', -1) + 1)
		)
	as number) column_value
from dual
connect by
	instr(:input_string, ',', 1, level - 1) > 0