Trim trailing space from table contents

Question

I have a table in a SQL server 2000 database with a nvarchar(30) field "details". There are some 10,000 records in that with a trailing space. I need a query to trim the particular field content in all rows. How can I achieve this?

Thanks.

Answer 1

If you wish to do this in a select statement only, use

SELECT RTRIM(Val)
FROM Table

If you wish to change the values in the table, use update

UPDATE Table
SET Val = RTRIM(Val)

For padding puposes you can use replicate

SELECT REPLICATE('*', 10) + 'TADA'

Answer 2

UPDATE table SET details = RTRIM(details)

For padding, you could do, for instance:

UPDATE table SET details = details + '    '

or

UPDATE table SET details = '    ' + details