RE: how to substr '%' from the data?
Thanks a lot, that works.
"MacGregor, Ian A." <[EMAIL PROTECTED]> wrote:
By using the instr function. Here is an example. N.B., the space between the words "and" and "seven" is returned.
select substr('Four score and seven years ago',1, instr('Four score and seven years ago', 'seven') -1) from dual
SUBSTR('FOURSCO---Four score and
Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
-Original Message-From: Helen rwulfjeq [mailto:[EMAIL PROTECTED]]Sent: Monday, June 11, 2001 4:07 PMTo: Multiple recipients of list ORACLE-LSubject: how to substr '%' from the data?
Hello,
I have data like following, how do I substr the string (or use any other function) to get until "%" and return without "%":
*
RC-SF-DAL-nd-%
AD-LE-%-%-%
RC-DD-LKF-01-RENTAL%
CS-%-%-%-%
CS-%-ABC-%-%
*
I did "select RTRIM('CS-%-%-%-%', '%-%') from DUAL", but it can not return correct for the last record
eg, "select RTRIM(CS-%-ABC-%-%', '%-%') from DUAL" -- will not work correctly
Thanks for help
Do You Yahoo!?Yahoo! Mail Personal Address - Get email at your own domain with Yahoo! Mail. Do You Yahoo!?
Yahoo! Mail Personal Address -
Get email at your own domain with Yahoo! Mail.
RE: how to substr '%' from the data?
By
using the instr function. Here is an example. N.B.,
the space between the words "and" and "seven" is
returned.
select substr('Four score and seven years
ago',1, instr('Four score and seven years ago', 'seven')
-1) from dual
SUBSTR('FOURSCO---Four score and
Ian
MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
-Original
Message-From: Helen rwulfjeq
[mailto:[EMAIL PROTECTED]]Sent: Monday, June 11, 2001 4:07
PMTo: Multiple recipients of list ORACLE-LSubject: how
to substr '%' from the data?
Hello,
I have data like following, how do I substr the string (or use any other
function) to get until "%" and return without "%":
*
RC-SF-DAL-nd-%
AD-LE-%-%-%
RC-DD-LKF-01-RENTAL%
CS-%-%-%-%
CS-%-ABC-%-%
*
I did "select RTRIM('CS-%-%-%-%', '%-%') from DUAL",
but it can not return correct for the last record
eg, "select RTRIM(CS-%-ABC-%-%', '%-%') from
DUAL" -- will not work correctly
Thanks for help
Do You Yahoo!?Yahoo! Mail Personal
Address - Get email at your own domain with Yahoo! Mail.
RE: how to substr '%' from the data?
Not exactly sure what you're after
but possibly the below if you're simply looking to stop before the first
occurence of '%'
select substr('CS-%-ABC-%-%', 1, instr('CS-%-ABC-%-%','%') - 1) from dual;
-Original Message-
Sent: 12 June 2001 00:07
To: Multiple recipients of list ORACLE-L
Hello,
I have data like following, how do I substr the string (or use any other
function) to get until "%" and return without "%":
*
RC-SF-DAL-nd-%
AD-LE-%-%-%
RC-DD-LKF-01-RENTAL%
CS-%-%-%-%
CS-%-ABC-%-%
*
I did "select RTRIM('CS-%-%-%-%', '%-%') from DUAL", but it can not return
correct for the last record
eg, "select RTRIM(CS-%-ABC-%-%', '%-%') from DUAL" -- will not work
correctly
Thanks for help
_
Do You Yahoo!?
Yahoo! Mail Personal
Address - Get email at your own domain with Yahoo! Mail.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Nicoll, Iain (Calanais)
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: how to substr '%' from the data?
Helen,
try this instead,, It should help
select substr(mystring,1,(instr(mystring,'%') -1))
from mytable
eg below
DEV>create mytable (mystring varchar2(10));
Table created
DEV>insert into mytable values ('abcdefgh%jkl');
1 row created
DEV>insert into mytable values ('a%cdefghjkl');
1 row created
DEV>commit;
Commit complete
DEV>select substr(mystring,1,(instr(mystring,'%') -1))
2 from mytable
3 /
substr(mystring,1,(
--
abcdefgh
a
2 rows selected
DEV>
Brian.
Helen rwulfjeq <[EMAIL PROTECTED]>@fatcity.com on 06/11/2001 06:06:35 PM
Please respond to [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Hello,
I have data like following, how do I substr the string (or use any other
function) to get until "%" and return without "%":
*
RC-SF-DAL-nd-%
AD-LE-%-%-%
RC-DD-LKF-01-RENTAL%
CS-%-%-%-%
CS-%-ABC-%-%
*
I did "select RTRIM('CS-%-%-%-%', '%-%') from DUAL", but it can not return
correct for the last record
eg, "select RTRIM(CS-%-ABC-%-%', '%-%') from DUAL" -- will not work
correctly
Thanks for help
Do You Yahoo!?
Yahoo! Mail Personal Address - Get email at your own domain with Yahoo!
Mail.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
how to substr '%' from the data?
Hello,
I have data like following, how do I substr the string (or use any other function) to get until "%" and return without "%":
*
RC-SF-DAL-nd-%
AD-LE-%-%-%
RC-DD-LKF-01-RENTAL%
CS-%-%-%-%
CS-%-ABC-%-%
*
I did "select RTRIM('CS-%-%-%-%', '%-%') from DUAL", but it can not return correct for the last record
eg, "select RTRIM(CS-%-ABC-%-%', '%-%') from DUAL" -- will not work correctly
Thanks for help
Do You Yahoo!?
Yahoo! Mail Personal Address -
Get email at your own domain with Yahoo! Mail.
