SQL how do I remove trailing spaces?

Question

I'm currently working in an SQL database where a column has trailing spaces.

The spaces in question show up as %20 in the browser url.

I've been able to remove them with a select query but whenever I convert it to an update an set query it doesn't seem to work, any input would be appreciated.

Working select query:

select [dbo].[udf-Str-Strip-Control](identifier)
from [AHDRC].[dbo].[artworks]

Broken update query:

update [AHDRC].[dbo].[artworks]
SET [identifier] = [dbo].[udf-Str-Strip-Control](identifier);                                         

SELECT [identifier]
From [AHDRC].[dbo].[artworks];

I am currently using SQL server management studio [identifier] is a nchar(128)

Apologies if anything is unclear / badly formatted.

Is it a `char`? That is a fixed length datatype, where a value shorter than its defined length is padded with spaces — HoneyBadger, Jul 06 '22 at 12:39
@HoneyBadger I've added your remarks, I'm using SQL server management studio 18 & [identifier] is a nvarchar(max). — DaSernet, Jul 06 '22 at 12:49
SQL server management studio is an editor, not a dbms. The dbms would be sql server. `string` is not a sql server datatype, its `(n)vartchar` or `(n)char` — HoneyBadger, Jul 06 '22 at 12:51
@HoneyBadger You hit the nail on the head with the char padding with spaces, thanks a lot for the explanation & additional info. — DaSernet, Jul 06 '22 at 12:53

score 0 · Answer 1 · answered Jul 06 '22 at 12:59

0

[identifier] was a nchar(128) causing trailing spaces.

answered Jul 06 '22 at 12:59

DaSernet

25
5

SQL how do I remove trailing spaces?

1 Answers1