SQL Help needed!!!

The Mighty Dubs

I am currently using SQL 2000 server and have a problem. I have a table which contains a number of columns with information about various different items. I need to add a new column at the end to contain information about the image whereabouts of the corresponding item. Now the item image is named the same as the item number i.e Item 123 has a corresponding image called 123.jpg. Is there a way I can take this information and insert it into the last column together with a predetermined location e.g.

C:\images\123.jpg to appear in the last column and this to happen in every column there after? As there are a couple of thousand items in the table i dont fancy manually doing this one by one...:eek:

Seth Brundle

Does the item number just contain numbers? (may sound like a daft question but I want to be sure!)
Assuming it does then just run a query that updates the table and adds a value to your image column like

UPDATE tblTable SET [imageCol]="C:\images\" + [item numberCol] + ".jpg"

The Mighty Dubs wrote:

C:\images\123.jpg to appear in the last column and this to happen in every column there after?

Are you confusing columns with rows?

The Mighty Dubs

cheers fro that will give that a lash now. And yes I was confusing columns with rows...Monday morning...what can i say;)

The Mighty Dubs

Ok I treid that but Im getting errors...Heres the statement


UPDATE tblItems SET imagelink = "Z:\" + SupplierID + "\" + SupplierCode + ".jpg"

And here are the errors

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Z:\'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name '\'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name '.jpg'.

Any ideas

stipey

you might need to escape the \ character in the path

so you would use \\ instead of \ - for example: Z:\\



..... actually, before even trying that, you should use ' for strings instead of "

The Mighty Dubs

stipey wrote:

..... actually, before even trying that, you should use ' for strings instead of "

Using ' and not " for the strings did the job. Cheers for that.

But as one problem gets sorted another rears its head. The way that information is being populated in the imagelink column is as follows

Z:\\AAA \35860.jpg.

There are 7 blanks spaces between the off the supplierid and the \. I need to remove the 7 blank spaces so it reads like so
Z:\\AAA\35860.jpg.

Is there a search replace type code that can be done for this by any chance?

Seth Brundle

MSSQL has LTrim() and RTrim() functions (but no Trim() function!). Use one or other of these in your SQL to remove the spaces.

bonkey

At the risk of sounding snotty....

MSSQL Has a REPLACE function.
If you had looked at the list of String Functions in the help, you'd have seen it.
If you had done a search on MSSQL REPLACE in google, you'd have seen it onm the first page of hits.
If you had done a search on Microsoft SQL Server replace in google, you'd have seen it on the first page of hits.

I don't mind helping, but seriously....it seems that part of your problem is that you don't know how to look for stuff. You knew you were looking for some sort of replace function in mssql. Did you not actually look?