OraFAQ Forum: SQL & PL/SQL » How do I extract last element of a string?

Home » SQL & PL/SQL » SQL & PL/SQL » How do I extract last element of a string? (

) 1 Vote

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How do I extract last element of a string? [message #199494]

Tue, 24 October 2006 15:23

Fred Easey
Messages: 73
Registered: January 2005

Member

Hi,

How do I extract the final element of a string eg:

'foo' -> 'foo'

'foo bar' -> 'bar'

'foo bar moo' -> 'moo'

etc etc

(I am on 10g, so do have regexp functions available)

Cheers,

Fred

Report message to a moderator

Re: How do I extract last element of a string? [message #199495 is a reply to message #199494]

Tue, 24 October 2006 15:31

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Code: [Select all] [Show/ hide]

SQL> with list as (
  2  select 'foo' id from dual union all
  3  select 'foo bar' from dual union all
  4  select 'foo bar moo' from dual)
  5  select regexp_substr(id,'[^ ]*?$') from list;

REGEXP_SUBS
-----------
foo
bar
moo

Report message to a moderator

Re: How do I extract last element of a string? [message #199663 is a reply to message #199495]

Wed, 25 October 2006 12:15

Fred Easey
Messages: 73
Registered: January 2005

Member

Thanks, much appreciated.

Report message to a moderator

Re: How do I extract last element of a string? [message #199682 is a reply to message #199663]

Wed, 25 October 2006 16:27

Fred Easey
Messages: 73
Registered: January 2005

Member

I managed to come up with this to get the first element of the string:

regexp_substr(id,'^[^ ]*')

More through trial and error though.

Could you explain how the previous reg exp to get surname actually works and also how the above reg_exp works?

(sorry to be a pain, just finding it really hard to get my head round how these things work)

Report message to a moderator

Re: How do I extract last element of a string? [message #199689 is a reply to message #199682]

Wed, 25 October 2006 20:38

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Regular expressions are definitely not an intuitive concept.

The '?' in my first regex can actually be removed and simplified to regexp_substr(id,'[^ ]*$').

To explain the expression, I'll start from the right hand side of the expression '[^ ]*$'. The '$' indicates to match from the end of the string. The '*' matches zero or more of the expression [^ ], which is essentially stating match as many non-space characters as possible. When a '^' is used inside a bracket expression, it is used to indicate negation. In this case it means anything EXCEPT a space. Therefore, when the first space from the right is encountered, the search stops and returns everything from the end of the string to right before the search stopped. The search will stop when the first space is encountered or if the end of the string is reached prior to a hitting a space (ie. no spaces are in the text). When NO space is encountered, then everything is returned, as indicated in the word 'foo' by itself.

Good job with your expression. Like mine above, yours can be further simplified to regexp_substr(id,'[^ ]*'). Doesn't really hurt anything to include the leading '^', but as you see below, it works without it too:

Code: [Select all] [Show/ hide]

SQL> with list as (
  2    select 'foo' id from dual union all
  3    select 'foo bar' from dual union all
  4    select 'foo bar moo' from dual)
  5    select regexp_substr(id,'^[^ ]*'),
  6      regexp_substr(id,'[^ ]*') from list;

REGEXP_SUBS REGEXP_SUBS
----------- -----------
foo         foo
foo         foo
foo         foo

By default, the search will begin from the left of the string (unless a '$' is used as explained above). Therefore, '[^ ]*' indicates to start from the beginning of the string and match as many non-space characters as possible. Once a space (or the end of the string) is encountered, the search will stop.

HTH

Report message to a moderator

Re: How do I extract last element of a string? [message #199720 is a reply to message #199689]

Thu, 26 October 2006 00:28

Frank
Messages: 7901
Registered: March 2000

Senior Member

Great explanation ebrian!

Report message to a moderator

Re: How do I extract last element of a string? [message #199871 is a reply to message #199720]

Thu, 26 October 2006 09:06

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Thanks Frank. When it comes to regex, never can tell if the explanation will make sense to others.

Report message to a moderator

Re: How do I extract last element of a string? [message #199932 is a reply to message #199494]

Fri, 27 October 2006 00:39

romi
Messages: 67
Registered: October 2006

Member

Try it:-

Select substr(column_name,-3,3) from table_name.

Report message to a moderator

Re: How do I extract last element of a string? [message #199941 is a reply to message #199932]

Fri, 27 October 2006 01:30

Frank
Messages: 7901
Registered: March 2000

Senior Member

@romi:
Did you actually read this thread? There was a perfect solution by ebrian already.
Your 'solution' only works if the last string contains exactly 3 characters, not really a practical solution, is it?

Report message to a moderator

Re: How do I extract last element of a string? [message #199944 is a reply to message #199932]

Fri, 27 October 2006 01:35

Littlefoot
Messages: 21824
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

LOL, this one is a joke of the day /forum/fa/451/0/

Code: [Select all] [Show/ hide]

SQL> select * From test;

NAME
--------------------
Frank Naude
William Robertson
Little Foot
E Brian

SQL> select substr(name, -3, 3) from test;

SUB
---
ude
son
oot
ian

SQL>

[EDIT] Bad, bad smiley.

[Updated on: Fri, 27 October 2006 03:22]

Report message to a moderator

Re: How do I extract last element of a string? [message #199972 is a reply to message #199689]

Fri, 27 October 2006 04:18

Fred Easey
Messages: 73
Registered: January 2005

Member

ebrian - thanks for the explanation, it's all starting to fall in to place now Smile

Report message to a moderator

Re: How do I extract last element of a string? [message #199980 is a reply to message #199494]

Fri, 27 October 2006 04:59

romi
Messages: 67
Registered: October 2006

Member

First of all,sorry.I have given my query only for asked value.

regexp functions works on oracle10g.But it doen't work on
oracle9i.So,in oracle 9i ,how can i extract.

Report message to a moderator

Re: How do I extract last element of a string? [message #199984 is a reply to message #199980]

Fri, 27 October 2006 05:16

ebrian
Messages: 2794
Registered: April 2006

Senior Member

To find the last element, you could use:

Code: [Select all] [Show/ hide]

SQL> select substr('foo bar moo', instr('foo bar moo', ' ',-1)+1) from dual;

SUB
---
moo

Report message to a moderator

Re: How do I extract last element of a string? [message #199985 is a reply to message #199980]

Fri, 27 October 2006 05:17

Littlefoot
Messages: 21824
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

One way would be use of SUBSTR, INSTR, LENGTH, ... functions. Another could be writing a PL/SQL procedure (loop and extract desired substring).

[Updated on: Fri, 27 October 2006 05:17]

Report message to a moderator

Re: How do I extract last element of a string? [message #199990 is a reply to message #199494]

Fri, 27 October 2006 05:37

romi
Messages: 67
Registered: October 2006

Member

well,
Littlefoot(LOL)

here is the answer of ur question:-

select substr(column_name,instr(column_name,' ',-1)+1) from table_name;

Previously,I gave answer only for asked value.

Report message to a moderator

Re: How do I extract last element of a string? [message #200015 is a reply to message #199990]

Fri, 27 October 2006 07:20

Littlefoot
Messages: 21824
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Yes, I've also seen Ebrian's post Cool

I apologize if I hurt your feelings; your previous solution gave correct result regarding sample data Fred Easey provided; I just thought that those "foo, bar, moo" shouldn't be taken too seriously.

Report message to a moderator

Previous Topic:	About ORA-01410: invalid ROWID error
Next Topic:	deleteing duplicates

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 21 06:21:17 CDT 2025