Home » SQL & PL/SQL » SQL & PL/SQL » plsql Substring function
plsql Substring function [message #194883] Mon, 25 September 2006 21:38 Go to next message
yguyon
Messages: 1
Registered: September 2006
Junior Member
In my database table, there is one field for the employee name in the order of (
LastName,FirstName) Example (Brian,Robbin,D) and I have another field for the Address in order of (City State Zip) Example (Clinton Twp Mi 48045).

Bellow I have a SQL query that actually work but what I need to do is to incorporate into this query where only the LastName is selected and where only the zip code is selected.

I believe I have to write a subtr clause for the zip but how to just select the lastname and not the LastName and full name.
Querry
------
Select Ballots.FULLNAME,
Ballots.ROWID,
Ballots.DATE_RETURNED,
Ballots.DATE_SENT,
Ballots.APP_RETURNED
from ABSENTEE_BALLOTS Ballots
Where trim(upper(address1)) like trim (upper(:street))|| '%' and trim (upper(address2)) = trim(upper(:zip)) and trim (upper(FULLNAME)) = trim(upper(:name)) and to_char(dob)=:birthDate ||"
Re: plsql Substring function [message #195026 is a reply to message #194883] Tue, 26 September 2006 11:55 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Extracting the last name:

substr(name, 1, instr(name, ',') - 1)

Extracting the zip code:

substr(address, instr(address, ' ', -1) + 1)
Cross-posted on OTN [message #195027 is a reply to message #195026] Tue, 26 September 2006 12:04 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
http://forums.oracle.com/forums/thread.jspa?threadID=427070&tstart=0
Re: plsql Substring function [message #195064 is a reply to message #194883] Tue, 26 September 2006 16:12 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
While the query works, your database design is, for lack of a better word, horrible. NEVER place multiple pieces of information in a single field. It causes nothing but headaches.
Previous Topic: Help with object type
Next Topic: From Integer to String in SQL
Goto Forum:
  


Current Time: Sat Dec 10 05:17:22 CST 2016

Total time taken to generate the page: 0.12586 seconds