Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Substitute for SUBSTR

Re: Substitute for SUBSTR

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Tue, 12 Jun 2007 04:46:29 -0700
Message-ID: <1181648789.064359.5680@i13g2000prf.googlegroups.com>


On Jun 12, 7:29 am, Buck <BuckTheManTurgid..._at_gmail.com> wrote:
> I am working on a system (SAP) which uses Oracle, but its language
> disallows the use of any Oracle functions, we can just use generic
> SQL.
>
> I need to do the following SUBSTR in SQL, without using SQL, comparing
> '010' against '010001'. Is there a way to do this in generic SQL?
>
> create table t_1 (
> pos_nr varchar2(3)
> );
>
> create table t_2 (
> ref_posid varchar2(10)
> );
>
> insert into t_1 (pos_nr) values ('010');
> insert into t_2 (ref_posid) values ('010001');
>
> commit;
>
> -- this is what I need to do, but substr not allowed.
> select * from t_1
> where exists (select 'match'
> from t_2
> where t_1.pos_nr = substr(ref_posid,1,3));
>
> -- so, how can we use "LIKE" or "BETWEEN"?
> select * from t_1
> where exists (select 'match'
> from t_2
> where t_1.pos_nr like ref_posid);

It seems a bit odd that an ERP package does not allow the use of SUBSTR. Try this:
SELECT
  T_1.*
FROM
  T_1,
  T_2
WHERE
  T_2.REF_POSID LIKE T_1.POS_NR||'%'; POS_NR



010

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Tue Jun 12 2007 - 06:46:29 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US