Re: Text manipulation in SQL

Presto, Figured it out.

Select 
(reverse(substr(reverse(trim(NAME)),  (INSTR(reverse(trim(_NAME)), '
', 1, 1)), (length(reverse(trim(_NAME))))))) as LASTNAME,
(substr(trim(NAME), (length(trim(NAME))-(INSTR(reverse(trim(NAME)), '
', 1, 1)))+2, (length(trim(NAME))))) as FIRSTNAME
From
blabla_table;

It may look nasty but it does do exacty what was required.



jim_esti@hotmail.com (Jim) wrote in message news:<f0e3dc0b.0106261359.7ab6b281@posting.google.com>...
> Hi All,
> 
> I have a column with a variety of names in it, for example
> John, Smith
> John, A S
> Jane, Doe A
> Jane, A
> 
> I will call this column NAMES for this.
> I was looking to manipulate that column.
> Specifically:
> The NAME field will be split after the rightmost blank.  Letter on the
> right of that blank will be displayed as LAST_NAME, the remainder will
> be displayed as FIRST_NAME.
> 
> Does anyone know how to do this correctly?
> 
> I have tried something like this:
> LTRIM( NAME, (substr(NAME,1,(INSTR(NAME,' ',1,1)))))
> But that would only yield something like this:
> John,
> John,
> Jane,
> Jane,
> 
> My little bit of code seems to only extract up to the first blank
> space.  Which would only work correctly if the names where only in two
> parts (like Jane, A).
> As I said I would like to return the NAME column in two parts (the
> letters to the right of the right most blank, and the other half).
> It seems a little trick to me.
> Anyone have any advice, hints, or solutions?