Thread: Query

    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2008
    Posts
    244
    Rep Power
    11

    Query


    Need a query to update a field...

    xxxx-001.jpg
    aaaaaa-001.jpg

    I need to remove the -001
    Or remove the last 8 characters an replace with .jpg

    xxxx.jpg
    aaaaaa.jpg

    Update Statement? Not sure how the syntax goes...
    Thanks
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2008
    Posts
    244
    Rep Power
    11
    Trying this but does not like it...

    UPDATE tbl_ SET Test = Left(JPG_Path,Len(JPG_Path)-8) + ".jpg"

    UPDATE tbl_ SET Test = Left(JPG_Path,Length(JPG_Path)-8) + ".jpg"

    UPDATE tbl_ SET Test = Left([JPG_Path],Length([JPG_Path])-8) + ".jpg"
    Last edited by jaykappy; March 25th, 2013 at 12:34 PM.
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    you're using square brackets around column names, and you're also using the plus sign for string concatenation

    i get the feeling you're not actually using the mysql database system, are you

    microsoft sql server, perhaps?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2008
    Posts
    244
    Rep Power
    11
    thanks for the help r937 appreciated...very constructive...

    I ran two queries....this did it...

    UPDATE tbl_ SET Test = Left(JPG_Path,Length(JPG_Path)-8)

    UPDATE tbl SET Test = CONCAT(Test , ".jpg" )

IMN logo majestic logo threadwatch logo seochat tools logo