RE: Using SQL to select records containing an embedded blank

I don't know if it performs any better, worse but I think it would be more accurate for finding imbedded blanks:

select *
from myFile
where trim(myField) like '% %'


Duane Christen


--


Duane Christen
Senior Software Engineer
(319) 790-7162
Duane.Christen@xxxxxxxxxx

Visit PAETEC.COM


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Elvis Budimlic
Sent: Thursday, August 13, 2009 10:39 AM
To: 'Midrange Systems Technical Discussion'
Subject: FW: Using SQL to select records containing an embedded blank

Had a typo in there, so correction:

SELECT * FROM myFile
WHERE POSITION(' ' IN myField) > 0 AND
POSITION(' ' IN myField) < LENGTH(RTRIM(myField))

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and OS/400 www.centerfieldtechnology.com


-----Original Message-----
From: Elvis Budimlic [mailto:ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx]
Sent: Thursday, August 13, 2009 10:37 AM
To: 'Midrange Systems Technical Discussion'
Subject: RE: Using SQL to select records containing an embedded blank

If I'm understanding you correctly, something like this ought to work:

SELECT * FROM myFile
WHERE POSITION(' ' IN myField) > 0 and
POSITION(' ' IN myField) < LENGTH(RTRIM(F1))

There are probably number of other ways to accomplish the same.

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and OS/400 www.centerfieldtechnology.com

-----Original Message-----
Subject: Using SQL to select records containing an embedded blank

Is it possible to use interactive sql on the i to find records where a particular field contains embedded blanks?

I mean one or more non-blanks followed by one or more blanks followed by one or more non-blanks.

I can do it with an RPG program, of course, but am trying to learn to use sql where appropriate.

Any suggestions?

Thanks,
Lance


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.