last word in text 
Author Message
 last word in text

Is there a function or composition of functions that will retrieve the last
word in a text field? I have a table of addresses with arbitrary text
for the first field (i.e. to whom it is addressed), and I'd like to do my
queries as something like this:

SELECT * from Addresses
WHERE state = 'CA'
ORDER BY lastword(addressee);

What I'm missing is the definition/name of the lastword function.

Note that I'm not expecting perfect results from this. There will be some
weirdness with certain addressees. Nonetheless, it is a reasonable
approach. My other option, which is what I'm doing now, is to have a "last"
column in the table and have my host language fill it on insertion. It's
workable, but it's redundant and overly complex from a data abstraction
point of view.

--Greg

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly



Sun, 03 Jul 2005 23:37:44 GMT
 last word in text

create function lastword(varchar) returns varchar as '
    return [string range $1 [string last " " $1] end]
' language 'pltcl';

Ian Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department

(253) 798-3549

Is there a function or composition of functions that will retrieve the last
word in a text field? I have a table of addresses with arbitrary text
for the first field (i.e. to whom it is addressed), and I'd like to do my
queries as something like this:

SELECT * from Addresses
WHERE state =3D 'CA'
ORDER BY lastword(addressee);

What I'm missing is the definition/name of the lastword function.

Note that I'm not expecting perfect results from this. There will be some
weirdness with certain addressees. Nonetheless, it is a reasonable
approach. My other option, which is what I'm doing now, is to have a "last"
column in the table and have my host language fill it on insertion. It's
workable, but it's redundant and overly complex from a data abstraction
point of view.

--Greg

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly



Mon, 04 Jul 2005 00:21:19 GMT
 last word in text
Ian Harding sez:

Quote:
} create function lastword(varchar) returns varchar as '
}     return [string range $1 [string last " " $1] end]
} ' language 'pltcl';

I don't seem to have pltcl available. In fact, all I have available is
plpgsql and sql. Can it be done in either of those languages?

Quote:
} Ian Harding

--Greg

---------------------------(end of broadcast)---------------------------



Mon, 04 Jul 2005 00:23:19 GMT
 last word in text
I am certain it can, but you could make pltcl available (if you have the ri=
ghts) by typing=20

createlang 'pltcl' yourdatabasename

at the command prompt.

Ian Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department

(253) 798-3549

Quote:

Ian Harding sez:
} create function lastword(varchar) returns varchar as '
}     return [string range $1 [string last " " $1] end]
} ' language 'pltcl';

I don't seem to have pltcl available. In fact, all I have available is
plpgsql and sql. Can it be done in either of those languages?

Quote:
} Ian Harding

--Greg

---------------------------(end of broadcast)---------------------------

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command



Mon, 04 Jul 2005 00:57:37 GMT
 last word in text
Ian Harding sez:

Quote:
} I am certain it can, but you could make pltcl available (if you have
} the rights) by typing
}
} createlang 'pltcl' yourdatabasename
}
} at the command prompt.

I have the rights, but I don't seem to have the library. I don't think I
compiled it when I configured/built postgresql in the first place.

% createlang --username=postgres --pglib=/Volumes/Database/postgresql/lib 'pltcl' template0
ERROR:  stat failed on file '/Volumes/Database/postgresql/lib/pltcl': No such file or directory
createlang: language installation failed
%

Quote:
} Ian Harding

--Greg

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org



Mon, 04 Jul 2005 03:31:56 GMT
 last word in text

Quote:

> Ian Harding sez:
> } I am certain it can, but you could make pltcl available (if you have
> } the rights) by typing
> }
> } createlang 'pltcl' yourdatabasename
> }
> } at the command prompt.

> I have the rights, but I don't seem to have the library. I don't think I
> compiled it when I configured/built postgresql in the first place.

> % createlang --username=postgres --pglib=/Volumes/Database/postgresql/lib 'pltcl' template0
> ERROR:  stat failed on file '/Volumes/Database/postgresql/lib/pltcl': No such file or directory
> createlang: language installation failed
> %

select substring(rtrim(' ', a.sentence) from '\w+$') from atable a;

Well at least for 7.3.x I can't remember but I presume you're still on an older
production release.

create function last_word ( text ) returns text as '
  declare
    frompos integer;
    ostr alias for $1;
  begin
    while strpos($ostr, ' ') > 0 loop
      frompos := strpos($ostr, ' ') + 1;
      ostr := substring($ostr, $frompos);
    end loop;
    return $ostr;
  end;
' language 'plpgsql';

It ain't pretty, it ain't perfect and it ain't optimised but it might do as a
first draft for you.

--
Nigel J. Andrews

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly



Mon, 04 Jul 2005 07:55:45 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Full Text Indexing of Rich Text/ Word Documents

2. Y2K--the last word

3. Last word of field

4. Session parameters (the last word)

5. Getting second to last word from a string

6. Detect and process last 2 words

7. VB% optimizing compiler - yeah right - THE LAST WORD

8. The last word on [more] [date] calculations

9. dts import failure: text file with CTRL-Z as last line

10. Going Nuts - TEXT/Blobs need to be the last field in a table

11. Problem with DTS and last text line.

12. DTS - Last Row in Fixed Width Text File Source Ignored


 
Powered by phpBB® Forum Software