Extract fields from delimited string?
Peter Jameson
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
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
Roy Hann
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.
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
_______________________________________________
Info-Ingres mailing list
Info-...@kettleriverconsulting.com
http://ext-cando.kettleriverconsulting.com/mailman/listinfo/info-ingres
nikosv
If you are comfortable with Perl and DBI you could just retrieve each row and break it apart as :
use 5.10.0;
$row="abc,defghi,jk1l,mnopq";
@parts=$row =~m/(\w+)/g;
say @parts[2];
#OR
@parts=split(",",$row);
say @parts[2];
pick whatever,they both work
>I don't think Ingres has a split function yet?
http://msdn.microsoft.com/en-us/library/orm-9780596101404-02-12.aspx
Could be a request for next years UKAUA ? if not like integrate the CLR but host another runtime ?
Peter Jameson
>
> My reply comes too late but I hadn't visited the forum for a while.
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
> 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 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.
Martin Bowes
> 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.
Martin Bowes
nikosv
true, this was also done with sql server but hosting the CLR is much easier than fiddling with C and the rest like linking etc and more high level
"Prior to SQL Server 2005, the main way that SQL Server was extended was using extended stored procedures that let you create external routines using programming languages such as C. Extended stored procedures are used like regular stored procedures, however, they can have performance problems such as memory leaks and can cause the server to become unreliable. CLR integration lets you extend SQL Server with the safety and reliability of T-SQL and with the flexibility of extended stored procedures."
nikosv
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?
>
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
****************************************