Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New to the CF scene
    Join Date
    Apr 2014
    Posts
    9
    Thanks
    3
    Thanked 0 Times in 0 Posts

    UPDATE and CAST data

    I need to UPDATE and CAST data from two columns with Type int into a single column with Type varchar. The new field is FullDocNo (varchar) with data being cast, concatenated, and copied from DocNo (int) and SequenceNo (int)

    Here is the UPDATE statement that I used on a MS Sql Server version of this db with success, but have not been able to find the equivalent for MySql by research and experimentation:

    UPDATE Sections SET FullDocNo = CAST(DocNo AS VARCHAR(5))+ '-' + CAST(SequenceNo AS VARCHAR(5))

    The error message is:

    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VARCHAR(5))+ '-' + CAST(SequenceNo AS VARCHAR(5))' at line 1

    Is there an equivalent MySql query that will accomplish this objective? Thanks in advance for any help that you can provide.

  • #2
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,912
    Thanks
    2
    Thanked 164 Times in 159 Posts
    You need to cast to a char, not varchar and you need to use the concat() function to join the two fields.

    This is untested, but should work.
    Code:
    UPDATE Sections SET FullDocNo = concat( CAST(DocNo AS CHAR(5)), '-', CAST(SequenceNo AS CHAR(5) );
    Alternate concat suntax where the first arg is the separator char used to join all remaining args (fields).
    Code:
    UPDATE Sections SET FullDocNo = concat_ws( '-', CAST(DocNo AS CHAR(5)), CAST(SequenceNo AS CHAR(5) );
    MySQL :: MySQL 5.0 Reference Manual :: 12.10 Cast Functions and Operators
    MySQL :: MySQL 5.0 Reference Manual :: 12.5 String Functions
    Last edited by FishMonger; 04-19-2014 at 03:19 PM.

  • Users who have thanked FishMonger for this post:

    dave1950 (04-19-2014)

  • #3
    New to the CF scene
    Join Date
    Apr 2014
    Posts
    9
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks for your prompt reply. I did try both versions, but got a 1064 error for both:

    MySQL said: Documentation
    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1


    I have looked over the code and compared to the Sections table and columns and don't see any problems. Any thoughts on how to proceed?

  • #4
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,912
    Thanks
    2
    Thanked 164 Times in 159 Posts
    Oops, it looks like I missed the closing paren on the second cast statement.

    Code:
    UPDATE Sections SET FullDocNo = concat( CAST(DocNo AS CHAR(5)), '-', CAST(SequenceNo AS CHAR(5)) );

  • Users who have thanked FishMonger for this post:

    dave1950 (04-19-2014)

  • #5
    New to the CF scene
    Join Date
    Apr 2014
    Posts
    9
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Yes, that fixed it. Thanks so much!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •