CODEWITHSUNDEEP
sqlstringsplitingres
Tomislav Mikulin
Tomislav Mikulin

Reputation: 5936

How to split a column by the number of white spaces in it with SQL?

I've got a single column that contains a set of names in it. I didn't design the database so that it contains multiple values in one column, but as it is I've got to extract that information now.

The problem is that in one field I've got multiple values like in this example:

"Jack Tom Larry                                                          Stan Kenny"

So the first three should be one group, and the other ones on the far right are another group. (Basically the only thing that separates them in the column is a specific number of whitespace between them, let's say 50 characters.)

How can I split them in pure SQL, so that I can get two columns like this:

column1 "Jack Tom Larry"
column2 "Stan Kenny"

Upvotes: 0

Views: 760

Answers (1)

G Jones
G Jones

Reputation: 367

A fairly simplistic answer would be to use a combination of left(), right() and locate(). Something like this (note I've substituted 50 spaces with "XXX" for readability):

declare global temporary table session.x(a varchar(100))
on commit preserve rows with norecovery;

insert into session.x values('Jack Tom LarryXXXStan Kenny');

select left(a,locate(a,'XXX')-1),right(a,length(a)+1-(locate(a,'XXX')+length('XXX'))) from session.x;

If you need a more general method of extracting the nth field from a string with a given separator, a bit like the split_part() function in PostgreSQL, in Ingres your options would be:

Write a user defined function using the Object Management Extension (OME). This isn't entirely straightforward but there is an excellent example in the wiki pages of Actian's community site to get you started: http://community.actian.com/wiki/OME:_User_Defined_Functions

Create a row-producing procedure. A bit more clunky to use than an OME function, but much easier to implement. Here's my attempt at such a procedure, not terribly well tested but it should serve as an example. You may need to adjust the widths of the input and output strings:

create procedure split
(
  inval = varchar(200) not null,
  sep = varchar(50) not null,
  n = integer not null
)
result row r(x varchar(200)) =

declare tno = integer not null;
        srch = integer not null;
        ptr = integer not null;
        resval = varchar(50);
begin
  tno = 1;
  srch = 1;
  ptr = 1;
  while (:srch <= length(:inval))
  do
    while (substr(:inval, :srch, length(:sep)) != :sep
      and :srch <= length(:inval))
    do
      srch = :srch + 1;
    endwhile;
    if (:tno = :n)
    then
      resval=substr(:inval, :ptr, :srch - :ptr);
      return row(:resval);
      return;
    endif;
    srch = :srch + length(:sep);
    ptr = :srch;
    tno = :tno + 1;
  endwhile;
  return row('');
end;

select s.x from session.x t, split(t.a,'XXX',2) s;

Upvotes: 1

Related Questions