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.
| Author |
Topic |
|
Apples
Posting Yak Master
146 Posts |
 Posted - 2008-04-01 : 14:06:23
|
| I have a small problem that I can't figure out. Here's my table:----------------------------------------Members----------------------------------------ID | Name | Image----------------------------------------When a user signs up to become a member, they can upload an image, which will be stored under Image. It will also be stored in a folder. For example, if the user with the ID of 14 uploads an image, it will be stored in 'livefiles/Members/14/image.jpg'. If the user does not upload an image, NULL is stored under Image, and the user has a default image, which is stored under 'livefiles/MembersDefault/1/image.jpg'.What I want to do is return the path of where the image is in the folders. Basically...If Image is not nullSELECT 'livefiles/Members/' + ID + '/' + Image AS ImagePathIf Image is nullSELECT 'livefiles/MembersDefault/1/image.jpg'I'd rather not do this with an IF statement, I'm doing a lot of other SELECTS, and I don't want to have to copy all that code. Here's what I tried...COALESCE (NULLIF('livefiles/Members/' + CONVERT(varchar(20), ID) + '/' + Image, 'livefiles/Members/' + ID + '/'), 'livefiles/MembersDefault/1/image.jpg') AS ImagePathbut that gave me an error saying:Conversion failed when converting the varchar value 'livefiles/Members/' to data type int. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-01 : 14:10:35
|
| SELECT CASE WHEN Image is not null then 'livefiles/Members/' + ID + '/' + Image ELSE 'livefiles/MembersDefault/1/image.jpg' END AS ImagePath |
 |
|
|
Apples
Posting Yak Master
146 Posts |
Posted - 2008-04-01 : 14:21:58
|
| visakh,Your code gave me the same error. However, if I take out the + signs, it works just fine.Is there another way to concatenate all the information together instead of using + signs? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-01 : 14:29:42
|
| what about this?SELECT CASE WHEN Image is not null then 'livefiles/Members/' + CAST(ID as varchar(10)) + '/' + Image ELSE 'livefiles/MembersDefault/1/image.jpg'END AS ImagePath |
|
|
|
Apples
Posting Yak Master
146 Posts |
Posted - 2008-04-01 : 14:36:48
|
| That worked, thanks! |
|
|
|
|
|
|
|
|
Select different strings