[Info-Ingres] Extract fields from delimited string?

[Peter Jameson]

Hello all,

I have a column which contains comma separated data e.g.

col1
-----
abc,defghi,jkl,mnopq
a,b,c,d,e
zxy,ggg,field3,field4,field5

Is it possible to write a select statement to extract field 3 (say) from
the comma separated string (for each row)?

So it would need to somehow locate the 3rd and 4th comma location and
extract the characters between those locations. I don't think Ingres
has a split function yet?

Cheers,

Peter
_______________________________________________
Info-Ingres mailing list
Info-...@kettleriverconsulting.com
http://ext-cando.kettleriverconsulting.com/mailman/listinfo/info-ingres

[Ingres Forums]

I don't think Ingres provides a function for this, but a row-producing
procedure might provide a way to achieve it (provided you're running a
fairly recent version of Ingres).
I've tried to create a quick example, see below. There may well be some
cases I haven't considered for which it doesn't work, but you'll get the
idea I'm sure.

GJ
(See Marty, I'm not all evil. Mwahahahahaha).


create procedure myproc123(a varchar(500) not null, b integer2)
result row (varchar(100))=
declare x=integer;
y=integer;
begin
x=1;
while (x < b)
do
select locate(a,',') into :y;
select right(a,length(a)-y) into :a;
x=x+1;
endwhile;
select locate(a,',') into :y;
select left(a,y-1) into :a;
return row (:a);
end;

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

insert into session.x values('first,second,third,fourth');
insert into session.x values('abc,def,ghi,jkl');
insert into session.x values('1,2');

select m.* from myproc123(x.a,3) m, session.x x;


--
geraintjones
------------------------------------------------------------------------
geraintjones's Profile: http://community.actian.com/forum/member.php?userid=57763
View this thread: http://community.actian.com/forum/showthread.php?t=14979

[Martin Bowes]

Hi Peter,



No Split function...but it's a nice idea.



You can use a table procedure to break the column into its components....

create procedure tp_split_on_comma(

string varchar(256) not null not default

)

result row my_row(element char(256) not null not default)

as declare

n integer not null not default;

len integer not null not default;

code_element varchar(256) not null not default;

begin

while (1 > 0)

do

n=position(',' in :string);

if (:n = 0) then

return row (:string);

endloop;

endif;

code_element=substring(string from 1 for :n - 1);

return row (:code_element);

len = length(:string);

string = right(string, len - n);

endwhile;

end;



select element from tp_split_on_comma('asdf, 12,foxtrot, alpha, romeo');

Executing . . .





+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

|element |

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

|asdf |

| 12 |

|foxtrot |

| alpha |

| romeo |

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+



You can munge that procedure easily enough to accept an index number to get specific about which one you want.



Marty

-----Original Message-----
From: Peter Jameson [mailto:pjam...@dummy.com]
Sent: 09 January 2013 11:18
To: info-...@kettleriverconsulting.com
Subject: [Info-Ingres] Extract fields from delimited string?



Hello all,



I have a column which contains comma separated data e.g.



col1

-----

abc,defghi,jkl,mnopq

a,b,c,d,e

zxy,ggg,field3,field4,field5



Is it possible to write a select statement to extract field 3 (say) from the comma separated string (for each row)?



So it would need to somehow locate the 3rd and 4th comma location and

extract the characters between those locations. I don't think Ingres

has a split function yet?



Cheers,



Peter

_______________________________________________

Info-Ingres mailing list

Info-...@kettleriverconsulting.com<mailto:Info-...@kettleriverconsulting.com>

http://ext-cando.kettleriverconsulting.com/mailman/listinfo/info-ingres

[Roy Hann]

Peter Jameson wrote:

> Hello all,
>
> I have a column which contains comma separated data e.g.
>
> col1
> -----
> abc,defghi,jkl,mnopq
> a,b,c,d,e
> zxy,ggg,field3,field4,field5
>
> Is it possible to write a select statement to extract field 3 (say) from
> the comma separated string (for each row)?
>
> So it would need to somehow locate the 3rd and 4th comma location and
> extract the characters between those locations. I don't think Ingres
> has a split function yet?

First Normal Form is a bitch huh?

No, Ingres doesn't have a split() function.

If you need the delimited portion of the string in the application I
think your best bet is to let the application extract it.

If you want to use the delimited portion as part of the restriction in a
WHERE clause you could try using the LIKE, or even better, the SIMILAR
TO predicate. For instance to find rows where the third field is 'jkl'
you could use:

create table noNF ( string varchar(30) not null );
insert into nonf values ( 'abc,defghi,jkl,mnopq' );
insert into nonf values ( 'a,b,c,d,e' );
insert into nonf values ( 'zxy,ggg,field3,field4,field5' );
insert into nonf values ( 'maresnest,hodgepodge,jkl' ); \g
select string from nonf where string similar to '%,%, *jkl *,%' or
string similar to '%,%, *jkl *'; \g


Executing . . .

+------------------------------+
|string |
+------------------------------+
|abc,defghi,jkl,mnopq |
|maresnest,hodgepodge,jkl |
+------------------------------+
(2 rows)
continue
*

--
Roy

UK Actian User Association Conference 2013 will be on Tuesday June 11. 2013.
The latest information is available from www.uk-iua.org.uk.

[Peter Jameson]

>
> My reply comes too late but I hadn't visited the forum for a while.
Yes too late but thank you for your reply anyway. :-)

In the end I unloaded the table into a flat file then used linux's cut
command to extract the fields I wanted and reloaded into a temp table
and carried on from there.

A split( ) style function would be a nice addition and ideal fodder for
a future "sprint" type exercise I'd of thought. Of course it does go
against relational database ideals and so might upset the purists a little.

[Roy Hann]

Peter Jameson wrote:

> A split( ) style function would be a nice addition and ideal fodder for
> a future "sprint" type exercise I'd of thought. Of course it does go
> against relational database ideals and so might upset the purists a little.

It shouldn't.

It does not violate any of the principles of relational theory. It's
just that relational theory doesn't care about it and doesn't require
it. There is no proscription against it.

--
Roy

UK Actian User Association Conference 2013 will be on Tuesday June 11. 2013.
The latest information is available from www.uk-iua.org.uk.

[Martin Bowes]

> >I don't think Ingres has a split function yet?

> this reminded me of SQL Server's CLR Integration where you can write UDF's using a programming language like C# hence you can have extended functionality where a stored proc cannot.

This functionality is already available in Ingres under the Object Management Extension. You can write your own C based function and incorporate it into Ingres.

Martin Bowes

[Roger Hill]

> On Monday, January 21, 2013 11:35:16 AM UTC+2, Martin Bowes wrote:
>> > >I don't think Ingres has a split function yet?
>

Here's a stored procedure I cobbled together (cribbed from somewhere else) that might be useful in
this context. It's written for DB2, so needs to be translated at least to some extent, and is
currently set up to process integers (carton numbers in the original context). I know I have used
a similar routine to do character fields without any problems.

YMMV.

Roger

create procedure hillr.test (IN p_string varchar(256))
-- p_string expected to be like '1,2,3'
-- this test proc splits out the comma separated list of
-- assumed integers, and presents them serially to the body of the proc.
language sql
specific test
begin
declare v_num integer default 1;
declare v_strlen integer;
declare v_carton integer;
declare v_wrkstr varchar(300);
declare v_substr varchar(300);
declare v_sqlstm varchar(256);

-- add a comma back and front, to eliminate first
-- and last character issues.
set v_wrkstr=',' || p_string || ',';

-- get the length of our working string
set v_strlen=length(v_wrkstr);

-- lopp though the string, getting in turn
-- each substring starting with the full string, and moving right
-- one character each time
while v_num <= v_strlen do

-- get the substring from the {v_num}th character to the end
select substr(v_wrkstr,v_num)
into v_substr
from sysibm.sysdummy1
;
-- assume we never have a zero carton number (could use -999 if needed)
set v_carton=0;

-- extract the carton number.
-- it's the substring starting from position 2
-- to the position of the next comma,
-- minus 2.

select cast(substr(v_substr,2,locate(',',v_substr,2)-2) as integer)
into v_carton

from sysibm.sysdummy1

-- only do this where our current substring
-- is more than one char(thus excluding the final comma)
-- and starts with a comma
where length(v_substr) > 1
and substr(v_substr,1,1)=',';

-- v_carton available here to do whatever you wish
-- so do something with it
set v_sqlstm='insert into hillrtest.tablex ' ||
'select ' ||
v_carton ||
' from sysibm.sysdummy1'||
' where '||
v_carton ||
' <> 0';


execute immediate v_sqlstm;

-- increment the counter to the next position in the string.

set v_num = v_num+1;
end while;


end
;


****************************************
Roger Hill
****************************************