Re: Creating a view with subscripting
scottishpoet wrote:
> Thanks TBP, I did say "something like ..." and that I hadn't tested mine.
>
Well, I was concerned why this wasn't doing what I expected, and all it
needed was a trim ;)
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 get_first(char (25));
create procedure get_first (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 = 1 to length(word_in)
let letter = substr(word_in,loop,1);
if letter = "," then
let first_or_last = "F";
let letter = "";
end if
if first_or_last = "L" then
let last_name = trim(last_name)||letter;
else
if first_or_last = "F" then
let first_name = trim(first_name)||letter;
end if
end if
end for
return (trim(first_name));
end procedure;
drop procedure get_last(char (25));
create procedure get_last (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 = 1 to length(word_in)
let letter = substr(word_in,loop,1);
if letter = "," then
let first_or_last = "F";
let letter = "";
end if
if first_or_last = "L" then
let last_name = trim(last_name)||letter;
else
if first_or_last = "F" then
let first_name = trim(first_name)||letter;
end if
end if
end for
return (trim(last_name));
end procedure;
select name, get_first(name) First, get_last(name) Last from names;
name first last
Surname, Firstname Firstname Surname
SurnameA, FirstnameA FirstnameA SurnameA
SurnB, FirstB FirstB SurnB
Geez