From: | jim_esti(at)hotmail(dot)com (Jim) |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Text manipulation in SQL |
Date: | 2001-06-27 15:24:06 |
Message-ID: | f0e3dc0b.0106270724.2d5f37dc@posting.google.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-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(at)hotmail(dot)com (Jim) wrote in message news:<f0e3dc0b(dot)0106261359(dot)7ab6b281(at)posting(dot)google(dot)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?
From | Date | Subject | |
---|---|---|---|
Next Message | Kristis Makris | 2001-06-27 15:55:57 | Re: Using the extract() function in plpgsql |
Previous Message | Jan Wieck | 2001-06-27 15:17:28 | Re: Storing image contents in TEXT fields |