• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Change a Substring with SQL

    April 5, 2006 Hey, Ted

    I am trying to change part of a character field with an SQL UPDATE command. However, my SQL command gives me an invalid token error at the opening parenthesis of SUBSTR in the SET statement. Is there a rule that you can’t use SUBSTR in a SET statement? If so, do you have a workaround?

    –Dennis

    What you’re trying to do is reasonable, Dennis. After all, RPG and CL let you change part of a character field using their %SUBST and %SST functions. But SQL is different.

    For the benefit of other readers, here is the SQL command Dennis sent to me.

    update library/file 
       set substr(rpc,1,3) = char(comno)
     where comno in (015,016) and substr(rpc,1,3) = ' '
    

    Dennis wants to replace the first three characters of the RPC field with whatever is in COMNO, but only if those characters are blank and only for records in companies 015 and 016. The solution is to replace the entire field, not just part of it, by selecting the parts you wish to keep and replacing the part you wish to update, like this:

    update library/file
       set rpc = char(comno) || substr(rpc,4)
     where comno in (015,016) and substr(rpc,1,3) = ' '
    

    In this example, the new value of RPC is the three company number digits concatenated to whatever is in positions four and following.

    Thanks for the question, Dennis. This is a handy technique to know.

    –Ted

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    ARCAD Software

    WEBINAR ROUNDTABLE
    True Git: Are there right and wrong ways to do Git on IBM i?
    February 25, 12 p.m. EST, 5 p.m. GMT

    Are you looking to manage your RPG and COBOL source code under Git? Our experts will reveal the different ways to use Git on IBM i and demonstrate how to measure the real ROI of Git on IBM i.

    Take your IBM i teams to the next level with Git.

    Register today!

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    California Software:  Migrate iSeries apps to Windows, Linux, or Unix
    Computer Keyes:  Rapidly convert *SCS printer files into black and white or full color PDF documents
    Bug Busters Software Engineering:  Quality software solutions for the iSeries since 1988

    Doug Fulmer to Leave IBM for Clear Technologies The Lowdown on S/36 and S/38 Compilers in i5/OS V5R5

    Leave a Reply Cancel reply

Volume 6, Number 14 -- April 5, 2006
THIS ISSUE SPONSORED BY:

WorksRight Software
Advanced Systems Concepts
TurboGorilla

Table of Contents

  • Avoiding the Green Screen of Death in RPG Programs
  • Change a Substring with SQL
  • Admin Alert: Protecting Your System from Critical Storage Errors

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • IBM Extends Dynamic Capacity Pricing Scheme To Its Cloud
  • Here’s What You Should Do About The IBM i Skills Shortage
  • Matillion Founder Recounts Midrange Roots
  • Four Hundred Monitor, February 24
  • IBM i PTF Guide, Volume 23, Number 8
  • iTech Solutions Keeps You In The Know With VERIFi
  • Tech Data’s Take On Certified Pre-Owned IT Gear
  • Guru: Prompting Stored Procedures
  • As I See It: Sunshine Or Oxygen?
  • Looking For Some Insight On IBM i Security

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2021 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.