Re: how to substr '%' from the data?

2001-06-11 Thread Brian_McQuillan 


Helen,
try this instead,, It should help
select substr(mystring,1,(instr(mystring,'%') -1))
from mytable

eg below
DEVcreate mytable (mystring varchar2(10));
Table created
DEVinsert into mytable values ('abcdefgh%jkl');
1 row created
DEVinsert into mytable values ('a%cdefghjkl');
1 row created
DEVcommit;
Commit complete
DEVselect 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).

RE: how to substr '%' from the data?

2001-06-11 Thread Nicoll, Iain (Calanais) 

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  http://personal.mail.yahoo.com/?.refer=mailiyfoot 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?

2001-06-11 Thread MacGregor, Ian A. 



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?

2001-06-11 Thread Helen rwulfjeq 
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.