Find the answer to your Linux question:
Results 1 to 6 of 6
i have a table with a column in it there are like 2000 entries down this column... the table name is "products" and the column is "products_image" example: 1234.jpg 321.jpg ...
Enjoy an ad free experience by logging in. Not a member yet? Register.
  1. #1
    Just Joined! herot's Avatar
    Join Date
    Dec 2005
    Posts
    41

    "find and replace" in SQL??


    i have a table with a column in it there are like 2000 entries down this column...
    the table name is "products" and the column is "products_image"

    example:

    1234.jpg
    321.jpg
    3456.jpg
    5678.jpg
    ...

    what SQL query should i use to replace *.jpg with *.gif down the entire column??
    i dont know squat about SQL...

  2. #2
    Linux Guru bigtomrodney's Avatar
    Join Date
    Nov 2004
    Location
    Ireland
    Posts
    6,133
    Globbing is not a good idea in SQL, as changes remain permanent. You will find there aren't many ways to do it either. There are no for loops in sequel. I would suggest using a select query to take all the details:
    Code:
    use your_database_name
    select products_image from picture
    then use a search/replace in your favourite text editor and then create (a rather repetitive) script for doing this.
    Code:
    update products set products_image = '1234.gif' where products_image = '1234.jpg'
    update products set products_image = '321.gif' where products_image = '321.jpg'
    This is a cursor, and it's not a clean or quick way to do it, but it is a way that you can verify your results beforehand by inspecting your values.
    It's not how any coder likes to do things, but with SQL I never take chances.

    PS Don't forget to backup before any bulk inserts!

  3. #3
    Just Joined! herot's Avatar
    Join Date
    Dec 2005
    Posts
    41
    Quote Originally Posted by bigtomrodney
    Globbing is not a good idea in SQL, as changes remain permanent. You will find there aren't many ways to do it either. There are no for loops in sequel. I would suggest using a select query to take all the details:
    Code:
    use your_database_name
    select products_image from picture
    then use a search/replace in your favourite text editor and then create (a rather repetitive) script for doing this.
    Code:
    update products set products_image = '1234.gif' where products_image = '1234.jpg'
    update products set products_image = '321.gif' where products_image = '321.jpg'
    This is a cursor, and it's not a clean or quick way to do it, but it is a way that you can verify your results beforehand by inspecting your values.
    It's not how any coder likes to do things, but with SQL I never take chances.

    PS Don't forget to backup before any bulk inserts!
    yeah, thats a good idea... it looks like it will do what i want...however, damage to the database in this case is not a concern... i back it up before executing *any* queries... and i can always just hit the restore button... (the backup and restore system is not on the same server as the SQL database..)

    can i do *.jpg and *.gif????

  4. #4
    Linux Guru bigtomrodney's Avatar
    Join Date
    Nov 2004
    Location
    Ireland
    Posts
    6,133
    Well the wildcard in SQL is % rather than * when you are working with strings. * means all. I'm not sure if it would work to do a %.jpg/%.gif. I know you can embed mysql statements into bash, maybe someone here can jump in and take over. Unfortunately my SQL experience is mainly from MSSQL Server, and I haven't done much with MySQL on and Unix systems.

  5. #5
    Just Joined! herot's Avatar
    Join Date
    Dec 2005
    Posts
    41
    Quote Originally Posted by bigtomrodney
    Well the wildcard in SQL is % rather than * when you are working with strings. * means all. I'm not sure if it would work to do a %.jpg/%.gif. I know you can embed mysql statements into bash, maybe someone here can jump in and take over. Unfortunately my SQL experience is mainly from MSSQL Server, and I haven't done much with MySQL on and Unix systems.
    yeah, im working on a linux machine and the database is in MySQL...

  6. #6
    Just Joined!
    Join Date
    Aug 2006
    Posts
    1
    I was looking for the same thing kinda...
    Here is what I got:

    UPDATE products
    SET products_image = stuff(products_image,len(products_image)-3,3,'gif')
    where (products_image like '%.jpg')


    Please note - I used this on SQL server

    You may have to fudge around with the numbers to lop-off the jpg and insert the gif.

    Hope this helps

Posting Permissions

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