how to "squeeze" a field of spaces

["Chevalier, Rick" <Rick.Chevalier@xxxxxxxxxxxxxxx>]

Joel,

You might be able to use some combination of LOCATE, POSITION, STRIP and CONCAT 
to accomplish this.  It would probably get pretty ugly though.  I think my 
suggestion would be to write a user defined function that receives a character 
field removes the unnecessary blanks and returns a result character field 
spaced properly.  It could be written in RPG or SQL and then used in your SQL 
statements.  An added benefit is that if it is written in RPG it could also be 
bound to any ILE program that might need to do the same thing.

HTH,

Rick Chevalier
AmeriCredit AITS

(817) 525-7178
rick.chevalier@xxxxxxxxxxxxxxx

 -----Original Message-----
From:   midrange-l-bounces+rick.chevalier=americredit.com@xxxxxxxxxxxx 
[mailto:midrange-l-bounces+rick.chevalier=americredit.com@xxxxxxxxxxxx]  On 
Behalf Of Stone, Joel
Sent:   Monday, January 05, 2004 6:35 PM
To:     'Midrange Systems Technical Discussion'
Subject:        SQL: how to "squeeze" a field of spaces

Can SQL squeeze double and triple spaces from a field to squish all words
together (with one space separating words)?

For example, change:


"John's    Grocery      Store"          to "John's Grocery Store"


and change:


"Home   Depot         Dallas branch"     to "Home Depot Dallas branch"?

Thanks!
_______________________________________________
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.