Midrange News for the IBM i Community


Posted by: Viking
Information Systems
CA
FYI: Right-adjust in SQL
has no ratings.
Published: 21 Feb 2013
Revised: 04 Mar 2013 - 1482 days ago
Last viewed on: 25 Mar 2017 (3268 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

COMMENTS

(Sign in to Post a Comment)
Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: FYI: Right-adjust in SQL
Posted: 4 years 30 days 4 hours 46 minutes ago

The SPACE function creates a string of blanks of the length specified? Interesting.

Posted by: Viking
Premium member *
CA
Comment on: FYI: Right-adjust in SQL
Posted: 4 years 27 days 21 hours 24 minutes ago

I looked up SPACE in my SQL book and it's not even listed, but it seems to work well!

Posted by: DaleB
Premium member *
Reading, PA
Comment on: FYI: Right-adjust in SQL
Posted: 4 years 27 days 8 hours 54 minutes ago

So not the IBM SQL Reference, then: http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzscaspace.htm. It's an ANSI scalar function. Works in SQL Server (T-SQL), too.

Posted by: DaleB
Premium member *
Reading, PA
Comment on: FYI: Right-adjust in SQL
Posted: 4 years 27 days 8 hours 46 minutes ago
Edited: Tue, 26 Feb, 2013 at 08:03:14 (1488 days ago)

Pondering . . . might this have better performance?

 

update myLib/myFile
  set myField = RIGHT(SPACE(LENGTH(myField)) + TRIM(myField), length(myField))
  where myField <> ' '

Either way, maybe add AND RIGHT(myField, 1) = ' ' to the WHERE clause, to avoid null updates.

 

Posted by: SteveCCNJ
Premium member *
Egg Harbor Twp., NJ
Comment on: FYI: Right-adjust in SQL
Posted: 4 years 21 days 3 hours 53 minutes ago

where VALUE(myField,'') <> '' would also see no difference between nulls and blanks.