Informix-sql beginner question
I have data in a column, seperated by a slash ( / ) which holds customer names. For example Smith/Bob/Mr The slash is always in a different location. How can I write a select statement to retrieve only the first name or only the last name ? Thanks Alot
Hi Steve,
You will have to write a Stored Procedure in order to retrieve either the
First Name or the last name in a Query.
It will be a lengthy SPL but it should work fine.
Assuming that the Length of the NAME column is 50 characters, you will have
to check the position (POS) of the '/' and then return the string from 1 to
POS-1 for the first name or POS+1 to 50 for the last name.
Your SPL could be as follows:
create procedure sp_first_name(name char(50) returning char(50))define POS smallint
let POS = 0
if name(1,1) = '/'
then
let POS=1
end if
if name(2,2) = '/'
then
let POS=2
end if
..... until name(50,50)
if POS=1
then
return null # That means no First name given
else
return name(1,POS-1) # returns first name
end if
end procedure
Create another procedure just like the one above for Last name, the only
difference will be that the return will be
return name(POS+1, 50)
HTH,
Gopi
Steve Cormier <scormier@idirect.com> wrote in message
news:sJYQ3.13242$jF3.95249@quark.idirect.com...
> I have data in a column, seperated by a slash ( / ) which holds customer
> names.
> For example Smith/Bob/Mr
>
> The slash is always in a different location.
>
> How can I write a select statement to retrieve only the first name
> or only the last name ?
>
> Thanks Alot
>
>
>
>
Break the column into three separate columns? This is a really bad table design, more trouble than it is worth. Art S. Kagel Steve Cormier wrote: > > I have data in a column, seperated by a slash ( / ) which holds customer > names. > For example Smith/Bob/Mr > > The slash is always in a different location. > > How can I write a select statement to retrieve only the first name > or only the last name ? > > Thanks Alot