Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Update column based on 1st letter

Author  Topic 

hummer123
Starting Member

5 Posts

Posted - 2014-02-25 : 04:08:25
Hi,

I have a large website with over 100,000 images and the location of the images are stored in a column (img_url) as below:

/images/imagename.jpg

Because all these images are stored in the same folder it is hard to manage so we want to store each image under a directory based on the 1st letter of the image name, ie:

/images/a/aimage.jpg
/images/b/bimage.jpg

I can automate the physical move of the images into the correct directory but I need help on a SQL update query that will update each column based on the 1st letter of the image.

So:

/images/aimage.jpg
/images/bimage.jpg

Updated to:

/images/a/aimage.jpg
/images/b/bimage.jpg

Can somebody help me in writing a query for this, I don’t really know where to start ;-)

Thanks !


nagino
Yak Posting Veteran

75 Posts

Posted - 2014-02-25 : 06:42:23
Like following?

UPDATE [YOUR TABLE]
SET img_url = stuff(img_url, 8, 0, SUBSTRING(img_url, 8, 2))


-------------------------------------
From Japan
Sorry, my English ability is limited.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-27 : 02:57:12
Question is will folder name be always first letter of image name? What if there are multiple images with same start letter? Will they all go into same folder?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

hummer123
Starting Member

5 Posts

Posted - 2014-02-27 : 03:32:54
Hi, yes the folder will always be the 1st letter of the image name, so for example there are 7454 images that start with the letter A , so they all need to be in the A folder.

Hope you can help

Thanks


quote:
Originally posted by visakh16

Question is will folder name be always first letter of image name? What if there are multiple images with same start letter? Will they all go into same folder?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-27 : 12:22:57
quote:
Originally posted by hummer123

Hi, yes the folder will always be the 1st letter of the image name, so for example there are 7454 images that start with the letter A , so they all need to be in the A folder.

Hope you can help

Thanks


quote:
Originally posted by visakh16

Question is will folder name be always first letter of image name? What if there are multiple images with same start letter? Will they all go into same folder?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs





Ok..then fine with suggestion given

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -