Creating a view with subscripting

I have a 'fullname' field in my Informix database that I would like to separate into a first name and a lastname field in a view. The fullname field is of the format "Lastname, Firstname Middle". Is there a way to get just the last name for the view? I considered using a case statement and looking for the first comman in the field, such as: case when fullname[2,2] = "," then fullname[1,1] when fullname[3,3] = "," then fullname[1,2] when fullname[4,4] = "," then fullname[1,3] . . . But I'm not sure if I can do a case statement in a view. Plus I wouldn't know how to get the first name. Any suggestions? TIA, John

probably best to write stored procedure that parses the fullname and returns 2 values, lastname and the rest. something like proc parse_name(fullname) returning lastname, rest define fullname, lastname, rest ... define i INT define value char(1) if fullname = NULL then do some error handling let i = 1 let value = fullname[i] while value != "," let lastname[i] = value let i = i+1 end while let rest = name[(i+1), length] -- i+1 becasue it needs to be the letter after the comma end procedure I haven't tried this and it probably need s tailoered a little, but hopefully gives you some idea. jwalchle@mvnu.edu (John W) wrote in message news:<957b8c22.0404151925.25574dda@posting.google.com>... > I have a 'fullname' field in my Informix database that I would like to > separate into a first name and a lastname field in a view. The > fullname field is of the format "Lastname, Firstname Middle". > > Is there a way to get just the last name for the view? I considered > using a case statement and looking for the first comman in the field, > such as: > > case > when fullname[2,2] = "," then fullname[1,1] > when fullname[3,3] = "," then fullname[1,2] > when fullname[4,4] = "," then fullname[1,3] > . > . > . > > But I'm not sure if I can do a case statement in a view. Plus I > wouldn't know how to get the first name. > > Any suggestions? > > TIA, > > John

I'll give that a try, Poet. Thanks! John dryburghj@yahoo.com (scottishpoet) wrote in message news:<81714288.0404160214.78e1e323@posting.google.com>... > probably best to write stored procedure that parses the fullname and > returns 2 values, lastname and the rest. > > something like > > proc parse_name(fullname) returning lastname, rest > define fullname, lastname, rest ... > define i INT > define value char(1) > > if fullname = NULL then > do some error handling > > let i = 1 > let value = fullname[i] > > while value != "," > let lastname[i] = value > let i = i+1 > end while > > let rest = name[(i+1), length] > -- i+1 becasue it needs to be the letter after the comma > > end procedure > > I haven't tried this and it probably need s tailoered a little, but > hopefully gives you some idea. > >

Had a play with this and I can't get the let i = 1; let value = fullname[i]; to pass the syntax test in my stored procedure... hmmm maybe need to dig the manuals out jwalchle@mvnu.edu (John W) wrote in message news:<957b8c22.0404160907.4121d553@posting.google.com>... > I'll give that a try, Poet. Thanks! > > John > > dryburghj@yahoo.com (scottishpoet) wrote in message news:<81714288.0404160214.78e1e323@posting.google.com>... > > probably best to write stored procedure that parses the fullname and > > returns 2 values, lastname and the rest. > > > > something like > > > > proc parse_name(fullname) returning lastname, rest > > define fullname, lastname, rest ... > > define i INT > > define value char(1) > > > > if fullname = NULL then > > do some error handling > > > > let i = 1 > > let value = fullname[i] > > > > while value != "," > > let lastname[i] = value > > let i = i+1 > > end while > > > > let rest = name[(i+1), length] > > -- i+1 becasue it needs to be the letter after the comma > > > > end procedure > > > > I haven't tried this and it probably need s tailoered a little, but > > hopefully gives you some idea. > > > >

scottishpoet wrote: > Had a play with this and I can't get the > > let i = 1; > let value = fullname[i]; > > to pass the syntax test in my stored procedure... > > hmmm maybe need to dig the manuals out > > jwalchle@mvnu.edu (John W) wrote in message news:<957b8c22.0404160907.4121d553@posting.google.com>... > >>I'll give that a try, Poet. Thanks! >> >>John >> >>dryburghj@yahoo.com (scottishpoet) wrote in message news:<81714288.0404160214.78e1e323@posting.google.com>... >> >>>probably best to write stored procedure that parses the fullname and >>>returns 2 values, lastname and the rest. >>> >>>something like >>> >>>proc parse_name(fullname) returning lastname, rest >>>define fullname, lastname, rest ... >>>define i INT >>>define value char(1) >>> >>>if fullname = NULL then >>> do some error handling >>> >>>let i = 1 >>>let value = fullname[i] >>> >>>while value != "," >>> let lastname[i] = value >>> let i = i+1 >>>end while >>> >>>let rest = name[(i+1), length] >>>-- i+1 becasue it needs to be the letter after the comma >>> >>>end procedure >>> >>>I haven't tried this and it probably need s tailoered a little, but >>>hopefully gives you some idea. >>> >>> Well, I must be sad to post this ... doing it backwards due to issues with concatenating strings like this : string = string||character resulted in null whereas string = character||string works ??|??|??| Still ... it's late drop table tab_strings; create table tab_strings (name char(25)); insert into tab_strings values ("Surname, Firstname"); insert into tab_strings values ("SurnameA, FirstnameA"); insert into tab_strings values ("SurnB, FirstB"); drop procedure lastname (char (25)); create procedure lastname (word_in char (25)) returning char(25)define first_name char(25); define last_name char(25); define first_or_last char(1); define letter char(1); define loop int; let first_name = ""; let last_name = ""; let first_or_last = "L"; for loop = length(word_in) to 1 let letter = substr(word_in,loop,1); if letter = "," then let first_or_last = "F"; let letter = ""; end if if first_or_last = "F" then let last_name = letter||last_name; else if first_or_last = "L" then let first_name = letter||first_name; end if end if end for return (last_name); end procedure; drop procedure firstname (char (25)); create procedure firstname (word_in char (25)) returning char(25)define first_name char(25); define last_name char(25); define first_or_last char(1); define letter char(1); define loop int; let first_name = ""; let last_name = ""; let first_or_last = "L"; for loop = length(word_in) to 1 let letter = substr(word_in,loop,1); if letter = "," then let first_or_last = "F"; let letter = ""; end if if first_or_last = "F" then let last_name = letter||last_name; else if first_or_last = "L" then let first_name = letter||first_name; end if end if end for return (first_name); end procedure; select lastname(name), firstname(name) from tab_strings; gives (expression) (expression) Surname Firstname SurnameA FirstnameA SurnB FirstB

scottishpoet wrote: > Had a play with this and I can't get the > > let i = 1; > let value = fullname[i]; > > to pass the syntax test in my stored procedure... > > hmmm maybe need to dig the manuals out > > jwalchle@mvnu.edu (John W) wrote in message news:<957b8c22.0404160907.4121d553@posting.google.com>... > >>I'll give that a try, Poet. Thanks! >> >>John >> >>dryburghj@yahoo.com (scottishpoet) wrote in message news:<81714288.0404160214.78e1e323@posting.google.com>... >> >>>probably best to write stored procedure that parses the fullname and >>>returns 2 values, lastname and the rest. >>> >>>something like >>> >>>proc parse_name(fullname) returning lastname, rest >>>define fullname, lastname, rest ... >>>define i INT >>>define value char(1) >>> >>>if fullname = NULL then >>> do some error handling >>> >>>let i = 1 >>>let value = fullname[i] >>> >>>while value != "," >>> let lastname[i] = value >>> let i = i+1 >>>end while >>> >>>let rest = name[(i+1), length] >>>-- i+1 becasue it needs to be the letter after the comma >>> >>>end procedure >>> >>>I haven't tried this and it probably need s tailoered a little, but >>>hopefully gives you some idea. >>> >>> I must be sad posting this ... but it's late I am doing it backwards due to some wierd concatenation behaviour ================================================================= drop table names; create table names (name char(25)); insert into names values ("Surname, Firstname"); insert into names values ("SurnameA, FirstnameA"); insert into names values ("SurnB, FirstB"); drop procedure lastname (char (25)); create procedure lastname (word_in char (25)) returning char(25)define first_name char(25); define last_name char(25); define first_or_last char(1); define letter char(1); define loop int; let first_name = ""; let last_name = ""; let first_or_last = "F"; for loop = length(word_in) to 1 let letter = substr(word_in,loop,1); if letter = "," then let first_or_last = "L"; let letter = ""; end if if first_or_last = "L" then let last_name = letter||last_name; else if first_or_last = "F" then let first_name = letter||first_name; end if end if end for return (last_name); end procedure; drop procedure firstname (char (25)); create procedure firstname (word_in char (25)) returning char(25)define first_name char(25); define last_name char(25); define first_or_last char(1); define letter char(1); define loop int; let first_name = ""; let last_name = ""; let first_or_last = "F"; for loop = length(word_in) to 1 let letter = substr(word_in,loop,1); if letter = "," then let first_or_last = "L"; let letter = ""; end if if first_or_last = "L" then let last_name = letter||last_name; else if first_or_last = "F" then let first_name = letter||first_name; end if end if end for return (first_name); end procedure; select lastname(name) lastname, firstname(name) firstname from names; ==================================================================== This returns ... lastname firstname Surname Firstname SurnameA FirstnameA SurnB FirstB 3 row(s) retrieved. I am sure there is a better, more efficient, error handling way to do this but hey, it's demonstrates a method. ====================================================================== Now the oddity in concatenation : ====================================================================== drop procedure concat_fail (char (25)); create procedure concat_fail (word_in char (25)) returning char(25)define word_out char(25); define loop int; let word_out = ""; for loop = 1 to length(word_in) let word_out = word_out||substr(word_in,loop,1); end for return (word_out); end procedure; drop procedure concat_work (char (25)); create procedure concat_work (word_in char (25)) returning char(25)define word_out char(25); define loop int; let word_out = ""; for loop = length(word_in) to 1 let word_out = substr(word_in,loop,1)||word_out; end for return (word_out); end procedure; select concat_work("abcdefg") concat_work, concat_fail("abcdefg") concat_fail from systables where tabid = 1; ====================================================================== returns concat_work concat_fail abcdefg 1 row(s) retrieved. 9.40.FC4W3

scottishpoet wrote: > Had a play with this and I can't get the > > let i = 1; > let value = fullname[i]; > > to pass the syntax test in my stored procedure... You can only use literal integers in substring operations written that way. You should use SUBSTR() instead - if you don't have that, you're either using OnLine 5.x or your server is too old and needs upgrading (or both). > hmmm maybe need to dig the manuals out > > jwalchle@mvnu.edu (John W) wrote in message news:<957b8c22.0404160907.4121d553@posting.google.com>... > >>I'll give that a try, Poet. Thanks! >> >>John >> >>dryburghj@yahoo.com (scottishpoet) wrote in message news:<81714288.0404160214.78e1e323@posting.google.com>... >> >>>probably best to write stored procedure that parses the fullname and >>>returns 2 values, lastname and the rest. >>> >>>something like >>> >>>proc parse_name(fullname) returning lastname, rest >>>define fullname, lastname, rest ... >>>define i INT >>>define value char(1) >>> >>>if fullname = NULL then >>> do some error handling >>> >>>let i = 1 >>>let value = fullname[i] >>> >>>while value != "," >>> let lastname[i] = value >>> let i = i+1 >>>end while >>> >>>let rest = name[(i+1), length] >>>-- i+1 becasue it needs to be the letter after the comma >>> >>>end procedure >>> >>>I haven't tried this and it probably need s tailoered a little, but >>>hopefully gives you some idea. >>> >>> -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/