Re: Substitute for SUBSTR

On Jun 12, 8:11 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Jun 12, 7:57 am, Buck <BuckTheManTurgid..._at_gmail.com> wrote:
>
> > > 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||'%';
>
> > I thought of that, but apparently the "||" is an Oracle construct, not
> > ANSI, and it is not allowed.
>
> > I haven't posted in the Oracle group for a few years. That Sybrand
> > guy, wow, is he STILL an asshole?
>
> SAP may be expecting a + rather than ||
>
> SELECT
> T_1.*
> FROM
> T_1,
> T_2
> WHERE
> T_2.REF_POSID LIKE T_1.POS_NR + '%';
>
> -or-
>
> SELECT
> T_1.*
> FROM
> T_1,
> T_2
> WHERE
> T_2.REF_POSID LIKE T_1.POS_NR + '*';
>
> It is my belief that Sybrand's comments are not always understood as
> he intended.
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.

If the plus sign does not work or even if it does you might want to try the CONCAT function which Oracle supports and is equivilent per the SQL manual to "||"

This thread would have been better suited to the comp.databases.oracle.misc board since this is not an RDBMS issue.

HTH -- Mark D Powell -- Received on Tue Jun 12 2007 - 09:11:48 CDT