DB2/400-AS/400

620 pts.
Tags:
AS/400
DB2
File:Student
.................
SID     . Sname .
.................
1       .  Chow .
2       .  Ratna.
.................
I want to update sname= Rotna(2nd position a=o) using substring? Please help the query or correct the below query:
UPDATE STUDENT SET SNAME=SUBSTR(SNAME, 2,1) WHERE SID=2
1

Answer Wiki

Thanks. We'll let you know when a new response is added.
UPDATE STUDENT SET SNAME='Rotna' where SUBSTR(SNAME, 2,1) = 'a'
If this is the only change needed. The code above will set every SNAME that has an 'a' in position 2 to 'o'.

Discuss This Question: 8  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.
  • philpl1jb

    This will take the first character of SNAME + '0' + third character on.

    the 50 will have to be adjusted to the length of the field-3. || is the concatenation symbol.

    UPDATE STUDENT

    SET SNAME=SUBSTRING(SNAME, 1,1) || 'o' || SUBSTRING(SNAME, 3, 50)

    WHERE SID=2

    54,090 pointsBadges:
    report
  • lance2221357

    Suggestion : and SUBSTR(SNAME,2,1)='a'

    UPDATE STUDENT

    SET SNAME=SUBSTR(SNAME, 1,1) || 'o' || SUBSTR(SNAME, 3, 50)

    WHERE SID=2  and SUBSTR(SNAME,2,1)='a'

    270 pointsBadges:
    report
  • chowas400
    I got  the below error when i execute the above query.. plz help me. my version is v5r3(mocha soft)


    620 pointsBadges:
    report
  • chowas400
    UPDATE STUDENT SET SNAME=SUBSTR(SNAME,1,1)||'O'||SUBSTR(SNAME,3,10)
    WHERE SID=2 AND SUBSTR(SNAME,2,1)='A'                              
    Token | was not valid. Valid tokens: <END-OF-STATEMENT>.          
    620 pointsBadges:
    report
  • lance2221357
    Hi, 
    V4R5 run is successfully. Pls. copy and past try again.

    UPDATE STUDENT SET SNAME=SUBSTR(SNAME,1,1)||'O'||SUBSTR(SNAME,3,07)
    WHERE SID='02' AND SUBSTR(SNAME,2,1)='a'                           
    1 rows updated in STUDENT in JLSHRDTA.                             
                                                                       
    270 pointsBadges:
    report
  • hunshabbir7
    I also try it on V5R1, it was ok
    2,900 pointsBadges:
    report
  • philpl1jb

    The problem here may be your emulator setting for | which might be set to broken bar.

    54,090 pointsBadges:
    report
  • chowas400
    yeah, philpl1jb..it's a emulator setting problem...

    I got it.. Thank you all
    620 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.

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

Following

Share this item with your network: