Home » SQL & PL/SQL » SQL & PL/SQL » help required in PLSQL
help required in PLSQL [message #324140] Sat, 31 May 2008 02:13 Go to next message
taqi10
Messages: 2
Registered: May 2008
Location: Karachi
Junior Member
A value in my column is "John Jack Ivor" , suppose that John Jack is the first name and Ivor is the Last Name , now I want to write a query which will return me the last name i.e "Ivor" , my background is totally of Java and C# and not of PLSQL , can any one please tell me that how to write this query which will return me the last name i.e "Ivor" , similarly if the column has the value "John Jack" , in this case John the first name and jack the last name than the query should return "Jack".

Please guide me I would really be thankful.

Regards,
Taqi Raza.
Re: help required in PLSQL [message #324143 is a reply to message #324140] Sat, 31 May 2008 02:24 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

How will oracle identy from the group of words that which word represents the last name from that for you....

Instade, you should have different columns if you have such requirements....


Regards..
Re: help required in PLSQL [message #324146 is a reply to message #324143] Sat, 31 May 2008 02:44 Go to previous messageGo to next message
taqi10
Messages: 2
Registered: May 2008
Location: Karachi
Junior Member
Dear vithalani_dipali,
Thanks a lot for your reply , however we cannot break up the column due to some reasons , for your first question that how will the query identify the last name , now I think that the strategey can be that :

1) First get the "Index number" of last empty space in the string which we got from the column (if this is possible in PLSQL)
2) Secondly we will get the substring from the String , the range of this sub string will be from length of the string till the index number of the last occurence of empty string (if possible in PLSQL).

This sub string will actually be the last name , I know the strategey to some extent but I do know not the excat syntax to write a query for achieving this strategey.I have done many similar things in Java but never in PLSQL , please guide me how to do this.

Taqi Raza.
Re: help required in PLSQL [message #324148 is a reply to message #324146] Sat, 31 May 2008 02:56 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Hi,

Have a look at the "Code Listing 3: Oracle9i Release 2 and later solution for varying IN lists" or the "str2tbl" funckton discussed here, I think you can adapt that to your needs.

Also, be aware that "PL/SQL" and "SQL" are two different beasts. Wink

Re: help required in PLSQL [message #324154 is a reply to message #324140] Sat, 31 May 2008 03:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
INSTR an SUBSTR

Also always post your Oracle version (4 decimals).

Regards
Michel
Re: help required in PLSQL [message #324177 is a reply to message #324154] Sat, 31 May 2008 04:39 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why do you, people, refuse to read OraFAQ Forum Guide? If you did that, you'd probably post all required information in the very first message and lots of sub-questions wouldn't be necessary. Moreover, Michel wouldn't have to repeat "Also always post your Oracle version (4 decimals)." as a budgerigar.

So, if your database version supports regular expressions, solution might be relatively simple:
SQL> WITH Test AS
  2    (SELECT 'John Jack Ivor' name FROM dual
  3     UNION
  4     SELECT 'Little Foot' name FROM dual
  5    )
  6    SELECT name,
  7           REGEXP_REPLACE(name, '^(.*) ') last_name
  8    FROM Test;

NAME           LAST_NAME
-------------- --------------
John Jack Ivor Ivor
Little Foot    Foot

SQL>
Re: help required in PLSQL [message #324207 is a reply to message #324177] Sat, 31 May 2008 10:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
LittleFoot,

Your query is perfectly correct accordingly to regular specifications that state that a joker expression must return the longest possible one, I think it is better (although far harder to read), when possible, to give a regular expression that does not rely on this. Also, it is faster as it immediate leads the engine to the solution without backtracking. But this depends on the implementation, some understand that "^.*" means "start to analyze from the end".
I mean using something like:
SQL> WITH Test AS
  2    (SELECT 'John Jack Ivor' name FROM dual
  3     UNION all
  4     SELECT 'Little Foot' name FROM dual
  5     UNION all
  6     SELECT 'Michel Cadot' FROM dual
  7    )
  8  SELECT name,
  9         REGEXP_REPLACE(name, '^.* ([^ ]*)$', '\1') last_name
 10  FROM Test;
NAME            LAST_NAME
--------------- ----------
John Jack Ivor  Ivor
Little Foot     Foot
Michel Cadot    Cadot

3 rows selected.

budgerigar? We say parrot here. Smile

Regards
Michel
Re: help required in PLSQL [message #324228 is a reply to message #324207] Sat, 31 May 2008 15:05 Go to previous message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
One of my first English lessons contained an advertisement. It said:
Quote:
Blue and yellow budgerigar flew away.
Can talk.
Says "You are crazy".

(Can't remember the rest of it, though)
Previous Topic: select index count on a different schema
Next Topic: How do I find a bad/corrupted record ?
Goto Forum:
  


Current Time: Tue Dec 06 08:49:40 CST 2016

Total time taken to generate the page: 0.10818 seconds