What is the easiest way using T-SQL / MS-SQL to append a string to existing table cells?

Question

I have a table with a 'filename' column. I recently performed an insert into this column but in my haste forgot to append the file extension to all the filenames entered. Fortunately they are all '.jpg' images.

How can I easily update the 'filename' column of these inserted fields (assuming I can select the recent rows based on known id values) to include the '.jpg' extension?

Answer 1

The solution is:

UPDATE tablename SET [filename] = RTRIM([filename]) + '.jpg' WHERE id > 50

RTRIM is required because otherwise the [filename] column in its entirety will be selected for the string concatenation i.e. if it is a varchar(20) column and filename is only 10 letters long then it will still select those 10 letters and then 10 spaces. This will in turn result in an error as you try to fit 20 + 3 characters into a 20 character long field.

Answer 2

Nice easy one I think.

update MyTable
set filename = filename + '.jpg'
where ...

Edit: Ooh +1 to @Graphain's answer for the rtrim suggestion.

Answer 3

Graphain's answer is correct if the column is a CHAR(20), but is not true if it was a VARCHAR(20) and the spaces hadn't been explicitly entered.

If you do try it on a CHAR field without the RTRIM function you will get a "String or binary data would be truncated" error.

Answer 4

@marxidad and @samjudson I'm not sure - the field I was working with was a varchar(50) and it included spacing which I agree does seem against the idea of the varchar. Perhaps someone can enlighten us as to why the varchar included spaces when selected?

Answer 5

If the original data came from a char column or variable (before being inserted into this table), then the original data had the spaces appended before becoming a varchar.

DECLARE @Name char(10), @Name2 varchar(10)
SELECT
  @Name = 'Bob',
  @Name2 = 'Bob'

SELECT
  CASE WHEN @Name2 = @Name THEN 1 ELSE 0 END as Equal,
  CASE WHEN @Name2 like @Name THEN 1 ELSE 0 END as Similiar

Life Lesson : never use char.

Answer 6

The answer to the mystery of the trailing spaces can be found in the ANSI_PADDING

For more information visit: SET ANSI_PADDING (Transact-SQL)

The default is ANSI_PADDIN ON. This will affect the column only when it is created but not to existing columns.

Before you run the update query, verify your data. It could have been compromised.

Run the following query to find compromised rows:

SELECT *
FROM tablename 
WHERE LEN(RTRIM([filename])) > 46 
-- The column size varchar(50) minus 4 chars 
-- for the needed file extension '.jpg' is 46.

These rows either have lost some characters or there is not enough space for adding the file extension.

Answer 7

I wanted to adjust David B's "Life Lesson". I think it should be "never use char for variable length string values" -> There are valid uses for the char data type, just not as many as some people think :)