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" <firstname.lastname@example.org> > 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: > email@example.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: firstname.lastname@example.org > +--- > +--- | 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: email@example.com +---
As an Amazon Associate we earn from qualifying purchases.
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.