How to separate text words from a column with commas?

Question

How to separate text words from a column with commas?

Navigation

#1 by (1 votes)

4

Is there a function ready in Firebird 2.5 that is the inverse of the list function?

When you do a

select list(campo,',') from tabela

I want to get the value of a field in a single column separated by a comma, I wanted a function that separated the field varchar into several columns

tabelax
codigo      informacao
 1          'alfa, beta, gama'
 2          'delta, peta, omega'

When doing select codigo,comando(informacao,',') as inf from tabelax it returns:

codigo  inf
  1     alfa
  1     beta
  1     gama
  2     delta
  2     peta
  2     omega

firebird

asked by anonymous 02.05.2014 / 14:33

1 answer

Enterprise Application Project problem with dependencies Test boot time in Ruby on Rails

score 1 · Accepted Answer

Unfortunately the firebird does not have a command for this, requiring the creation of a StoreProcedure to do this. It is necessary to create a strore procedure and use it in sql.

Example:

set term ^ ;

create or alter procedure f_retorna_lista(in_id integer,in_lista varchar(550))
returns (out_id integer, out_str varchar(100) )
as
declare variable var_pos integer;
declare variable var_next_pos integer;
declare variable var_length integer;
begin
  var_length = char_length(:in_lista);
  var_pos = 1;
  while (var_pos <= var_length) do
  begin
  var_next_pos = position(',',:in_lista,:var_pos);
  if (:var_next_pos = 0) then
    var_next_pos = var_length+1;
  out_str = trim(substring(:in_lista from :var_pos for (:var_next_pos - :var_pos)));
  out_id = in_id;
  suspend;
  var_pos = var_next_pos+1;
  end
end^

where I was running select t.codigo, sp.out_str from tabelax t left join (f_retorna_lista(codigo,informacao)) sp on sp.out_id = t.codigo