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