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 - 1663 days ago
Last viewed on: 20 Sep 2017 (3831 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)

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 6 months 29 days 18 hours 23 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 6 months 27 days 11 hours 1 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 6 months 26 days 22 hours 31 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 6 months 26 days 22 hours 24 minutes ago
Edited: Tue, 26 Feb, 2013 at 08:03:14 (1669 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 6 months 20 days 17 hours 31 minutes ago

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