Midrange News for the IBM i Community

Posted by: Viking
Information Systems
FYI: Right-adjust in SQL
has no ratings.
Published: 21 Feb 2013
Revised: 04 Mar 2013 - 2453 days ago
Last viewed on: 20 Nov 2019 (5078 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

FYI: Right-adjust in SQL Published by: Viking on 21 Feb 2013 view comments(5)

I have a file with a field that I needed to right-adjust the character values.  These values are of various lengths, and in many records they were left-adjusted while in others they were correctly right-adjusted.

After correcting the program that was writing them left-adjusted, I needed to fix the existing records.  I was surprised to discover that there's no simple function to right-adjust a value in SQL.  I was thinking I would have to write an RPG to run through all the records and do EvalR myField = %trim(myField);

But then I found a post somewhere (from Birgitta Hauser, I think) that showed how to do it in SQL.  This is what I ended up with:


update myLib/myFile                                 
   set myField = space(length(myField) - length(trim(myField))) concat trim(myfield)                    
   where myField <> ' '


Voila!  Hope maybe someone else finds this useful too.



Return to midrangenews.com home page.
Sort Ascend | Descend