SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Update column based on 1st letter
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hummer123
Starting Member

Cyprus
5 Posts

Posted - 02/25/2014 :  04:08:25  Show Profile  Reply with Quote
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

Japan
68 Posts

Posted - 02/25/2014 :  06:42:23  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 02/27/2014 :  02:57:12  Show Profile  Reply with Quote
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

Cyprus
5 Posts

Posted - 02/27/2014 :  03:32:54  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 02/27/2014 :  12:22:57  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000