Re: SQL question

["Bart Verweijen" <bart.verweijen@xxxxxxxxxxx>]

Hi,

Select * from xxx where Char_length(rtrim(expression)) = 13

did the trick for me !

Thanks for all the responses !

Bart

----- Original Message -----
From: "Peter Dow" <pcdow@MailAndNews.com>
To: <MIDRANGE-L@midrange.com>
Sent: Saturday, January 20, 2001 7:57 AM
Subject: Re: SQL question


> Hi Buck,
>
> I was reading the manual at IBM's Info Center and it seems to imply that
> doing
>
>     SELECT * FROM xxx WHERE LENGTH(RTRIM(expression)) = 13
>
> won't work:
>
> "The length attribute of the result is the same as the length attribute of
> expression. The actual length of the result is the length of the
expression
> minus the number of bytes removed. If all characters are removed, the
result
> is an empty string."
>
> Yet when I tried LENGTH(RTRIM(name)) on a file, it shows the truncated
> length. I guess what they mean by "length attribute" is the defined or
> maximum length, and the "actual length" is what is reported by the LENGTH
> function. Hmm. In any case, it appears that LENGTH(RTRIM(expression))
gives
> the same result as your UDF, so you could test your UDF by comparing
> results.
>
> HTH,
> Peter Dow
> Dow Software Services, Inc.
> 909 425-0194 voice
> 909 425-0196 fax
>
>
>
> ----- Original Message -----
> From: "Buck Calabro" <buck.calabro@aptissoftware.com>
> To: <MIDRANGE-L@midrange.com>
> Sent: Friday, January 19, 2001 2:35 PM
> Subject: RE: SQL question
>
>
> > Bart Verweijen wrote:
> >
> > > I need a SQL that copies all this data to a new type where the eancode
> > > length = 13.  I don't know what function I
> > >can use to get those ean-codes with length = 13.
> >
> > I mis-understood your original question.  If I understand rightly, you
> need
> > a "length" function so you can select * from xxx where len(EANCODE)=13.
> > Since CHARLEN and LENGTH work best with variable length string fields, I
> > wrote a UDF (User Defined Function) that does an RPG CHECKR to find the
> > length of the data within the field.  This *seems* to work, but you
might
> > want to do a lot of testing on it to be sure.  It's my first UDF.  I was
> > unable to look at it in debug - it runs in another thread and won't
break
> > for me.  I may yet be doing something wrong.
> >
> > Anyway, here's the code.  I owe David Morris thanks for pointing me to
the
> > April 2000 article in Midrange Computing.
> > http://www.midrangecomputing.com/mc/article.cfm?titleid=a289&md=20004
> >
> > h debug dftactgrp(*no) actgrp(*caller)
> >  * dbgview(*list)
> >
> >  * Test the length of an SQL string User Defined Function.
> >  * to register:
> >
> >  * CREATE FUNCTION STRLEN (VARCHAR(256))
> >  * RETURNS INTEGER
> >  * RETURNS NULL ON NULL INPUT
> >  * LANGUAGE RPGLE
> >  * EXTERNAL NAME 'BUCK/SQLUDFLEN'
> >  * DETERMINISTIC
> >  * NO SQL
> >  * NO EXTERNAL ACTION
> >  * PARAMETER STYLE SQL
> >  * DISALLOW PARALLEL
> >
> > d inpChar         s            256    varying
> > d outLen          s             10i 0
> > d inpInd          s              5i 0
> > d outInd          s              5i 0
> > d outSQLState     s              5
> > d inpFuncName     s            139    varying
> > d inpSpecName     s            128    varying
> > d outDiagTxt      s             70    varying
> >
> > c     *entry        plist
> > c                   parm                    inpChar
> > c                   parm                    outLen
> > c                   parm                    inpInd
> > c                   parm                    outInd
> > c                   parm                    outSQLState
> > c                   parm                    inpFuncName
> > c                   parm                    inpSpecName
> > c                   parm                    outDiagTxt
> >
> > c     ' '           checkr    inpChar       outLen
> >
> > c                   eval      *inlr = *on
> >
> > Buck Calabro
> > Aptis; Albany, NY
> > "Nothing is so firmly believed as
> >  that which we least know" -- Michel Montaigne
> > Visit the Midrange archives at http://www.midrange.com
> > +---
> > | This is the Midrange System Mailing List!
> > | To submit a new message, send your mail to MIDRANGE-L@midrange.com.
> > | To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
> > | To unsubscribe from this list send email to
> MIDRANGE-L-UNSUB@midrange.com.
> > | Questions should be directed to the list owner/operator:
> david@midrange.com
> > +---
>
> +---
> | This is the Midrange System Mailing List!
> | To submit a new message, send your mail to MIDRANGE-L@midrange.com.
> | To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
> | To unsubscribe from this list send email to
MIDRANGE-L-UNSUB@midrange.com.
> | Questions should be directed to the list owner/operator:
david@midrange.com
> +---
>

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---