SQL to edit last character in a field using STRSQL

385 pts.
Tags:
AS/400
SQL
I can select the rows using this SQL how can I update?

UPDATE mbmtest01/itemaster SET right (ITNBR,1) = ' ' WHERE right(ITNBR,1) = 'M'

Answer Wiki

Thanks. We'll let you know when a new response is added.

See discussion below.

Discuss This Question: 11  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    You could use the CONCAT, CHAR_LENGTH and LEFT functions (if they are available on your platform) or their equivalents. Something like this:
    UPDATE mbmtest01/itemaster 
    SET ITNBR = CONCAT(LEFT(ITNBR,CHAR_LENGTH(ITNBR)-1),' ') 
    WHERE right(ITNBR,1) = 'M'
    69,920 pointsBadges:
    report
  • TomLiotta
    The above UPDATE statement looks reasonable enough. Be aware that using this on fixed-length columns that can have trailing blanks might require the use of %TRIM() to locate the last significant character. Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    Carlos, I'm not certain that the AS/400 SQL has CHAR_LENGTH this will remove trailing 'M' UPDATE mbmtest01/itemaster SET ITNBR = CONCAT(substring(ITNBR,1, LENGTH(trim(ITNBR))-1),’ ‘) WHERE substring(ITNBR, LENGTH(trim(ITNBR)),1) = ‘M’ but will, I fear, fail if the field is blank. Phil
    50,375 pointsBadges:
    report
  • carlosdl
    Yes, it could fail if the field is blank, but the use of negative numbers as parameters for functions like SUBSTRING is allowed in other platforms.
    69,920 pointsBadges:
    report
  • nitzinger
    When I try this code: UPDATE mbmtest01/itemaster SET ITNBR = CONCAT(LEFT(ITNBR,CHAR_LENGTH(ITNBR)-1),’ ‘) WHERE right(ITNBR,1) = ‘M’ I get an: Duplicate key value specified. error How could that be happening Thanks to all
    385 pointsBadges:
    report
  • carlosdl
    Sounds like you have a unique index (or pk) on ITNBR, and when applying this update it would generate duplicates on that column.
    69,920 pointsBadges:
    report
  • nitzinger
    Yes it is a unique index. Is there a way around this?
    385 pointsBadges:
    report
  • TomLiotta
    I’m not certain that the AS/400 SQL has CHAR_LENGTH... The CHARACTER_LENGTH() (and CHAR_LENGTH()) function has been in DB2 for a long time. Not used much, since LENGTH() is shorter. Tom
    125,585 pointsBadges:
    report
  • carlosdl
    Hmmm. There is no way you can insert duplicate values or create them with an update on a column with a unique index. If the column has a unique index on it, it means that no duplicates should exist for that column, so, you should not try to create duplicates. If duplicates are going to be allowed, then you should drop the unique index. You might want to review the results you would get with this update (what rows are going to be affected ? how are they going to be affected ?), and make sure this is the correct way to achive your objectives.
    69,920 pointsBadges:
    report
  • thongtarget21
    This is great info. Thanks!
    485 pointsBadges:
    report
  • nitzinger
    You're right the key is unique and as I reviewed the data there is a few fields that removing the trailing character 'M' would cause them to become duplicates. No secondary key so this would cause other problems if allowed to proceed. Thanks you are so very right! This IS great stuff.
    385 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following