Home » SQL & PL/SQL » SQL & PL/SQL » How do I extract last element of a string?  () 1 Vote
How do I extract last element of a string? [message #199494] Tue, 24 October 2006 15:23 Go to next message
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
Re: How do I extract last element of a string? [message #199495 is a reply to message #199494] Tue, 24 October 2006 15:31 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
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
Re: How do I extract last element of a string? [message #199663 is a reply to message #199495] Wed, 25 October 2006 12:15 Go to previous messageGo to next message
Fred Easey
Messages: 73
Registered: January 2005
Member

Thanks, much appreciated.
Re: How do I extract last element of a string? [message #199682 is a reply to message #199663] Wed, 25 October 2006 16:27 Go to previous messageGo to next message
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)
Re: How do I extract last element of a string? [message #199689 is a reply to message #199682] Wed, 25 October 2006 20:38 Go to previous messageGo to next message
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:

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
Re: How do I extract last element of a string? [message #199720 is a reply to message #199689] Thu, 26 October 2006 00:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Great explanation ebrian!
Re: How do I extract last element of a string? [message #199871 is a reply to message #199720] Thu, 26 October 2006 09:06 Go to previous messageGo to next message
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.
Re: How do I extract last element of a string? [message #199932 is a reply to message #199494] Fri, 27 October 2006 00:39 Go to previous messageGo to next message
romi
Messages: 67
Registered: October 2006
Member

Try it:-

Select substr(column_name,-3,3) from table_name.
Re: How do I extract last element of a string? [message #199941 is a reply to message #199932] Fri, 27 October 2006 01:30 Go to previous messageGo to next message
Frank
Messages: 7880
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?
Re: How do I extract last element of a string? [message #199944 is a reply to message #199932] Fri, 27 October 2006 01:35 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
LOL, this one is a joke of the day ./fa/451/0/
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 Go to previous messageGo to next message
Fred Easey
Messages: 73
Registered: January 2005
Member

ebrian - thanks for the explanation, it's all starting to fall in to place now Smile
Re: How do I extract last element of a string? [message #199980 is a reply to message #199494] Fri, 27 October 2006 04:59 Go to previous messageGo to next message
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.
Re: How do I extract last element of a string? [message #199984 is a reply to message #199980] Fri, 27 October 2006 05:16 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
To find the last element, you could use:

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

SUB
---
moo


Re: How do I extract last element of a string? [message #199985 is a reply to message #199980] Fri, 27 October 2006 05:17 Go to previous messageGo to next message
Littlefoot
Messages: 20888
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 Go to previous messageGo to next message
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.
Re: How do I extract last element of a string? [message #200015 is a reply to message #199990] Fri, 27 October 2006 07:20 Go to previous message
Littlefoot
Messages: 20888
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.
Previous Topic: About ORA-01410: invalid ROWID error
Next Topic: deleteing duplicates
Goto Forum:
  


Current Time: Sat Dec 03 01:20:31 CST 2016

Total time taken to generate the page: 0.05253 seconds