CODEWITHSUNDEEP
t-sqltrim
joaodavidmateus
joaodavidmateus

Reputation: 93

T-SQL trim not working - let spaces on the result

I have a trim function that apply ltrim and rtrim

CREATE FUNCTION dbo.TRIM(@string VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
RETURN LTRIM(RTRIM(@string))
END
GO

I do the following query:

SELECT distinct dbo.trim([subject]) as subject
FROM [DISTR]

The result has rows like:

"A"
"A  "
"B"
...

I thought that thoose chars maybe weren't spaces but when I got the ascii code, it returns 32 which is the code for space.

My only guess is that I had to change the collaction of the database to: SQL_Latin1_General_CP1_CI_AI

Can that be the problem? Any ideas? Thanks

Upvotes: 2

Views: 3187

Answers (1)

Rodrigo
Rodrigo

Reputation: 4395

Maybe your field contains more than spaces. Remember than " " could be a space, tab, and many other "blank" chars. It's possible to match it using ASCII or building a CLR implementation of trim that uses regular expressions

Upvotes: 1

Related Questions