Re: SQL help on how to extract chars from a string?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 09 Feb 1999 16:59:25 GMT
Message-ID: <36c46951.14380818@192.86.155.100>

A copy of this was sent to <yliu_at_creighton.edu> (if that email address didn't require changing) On Tue, 9 Feb 1999 10:30:10 -0600, you wrote:

>Hi All,
>
>I need some help on how to use SQL to extract names from a string. My
>strings all look like the following string:
> name changed from "Yongge Liu" on date...
>The strings are 60 chars long. The names are variable length (I don't
>have any ideas how long the name is), but they are enclosed in quote (e.g.
>"Yongge Liu" in this example). I know the starting point of the name, but
>I do not know the ending position (I do know they are always ended with a
>double quote). I already checked out substr function, I don't think the
>substr function can do the job in my case. Can I write up some PL/SQL
>function to do this?
>

substr+instr can do this:

SQL> create table t ( str varchar2(255) ); Table created.

SQL> insert into t values ( 'name changed from "Yongge Liu" on date' ); 1 row created.

SQL> select substr( str, instr( str, '"')+1,

  2                      instr( str, '"', 1, 2 )-instr( str, '"')-1 ) name

  3 from t
  4 /

NAME

---

Yongge Liu

>Thank you so kindly in advance.
>
>Best regards
>
>Yongge
>yliu_at_creighton.edu
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Feb 09 1999 - 10:59:25 CST