CODEWITHSUNDEEP
sqlt-sql
LONG
LONG

Reputation: 4620

what will be the result of ltrim or rtrim on whitespace string

Example:

 SELECT 'A' + ltrim('    ') + 'B'  --or SELECT 'A' + rtrim('    ') + 'B'

Output:

AB

So what will be the actual character after trim a whitespace string? Based on the document, the result type will either be varchar or nvarchar, so it cannot be NULL, and of course if it is NULL, it will not output AB. As there is nothing between A and B, it is not neither whitespace and NULL, what is the actual output, or what is the corresponding varchar or nvarchar for that missing character? Any ASCII reflects that?

UPDATE:

After run this: 

select ASCII(rtrim('    '))

The result is NULL, but why A + NULL + B will output AB instead of AB?

Thanks :)

Upvotes: 0

Views: 281

Answers (1)

Stefanos Zilellis
Stefanos Zilellis

Reputation: 611

The ASCII function produces the null result, not the TRIM function:

SELECT ASCII('')--NULL
SELECT ASCII(rtrim('    '))--NULL
SELECT rtrim('    ')--empty string
SELECT 'A' + ltrim('    ') + 'B'--'A' + empty string + 'B' = 'AB'

The data type of the concatenation result now depends on implicit conversions on which the constraints take priority:

SELECT CAST(sql_variant_property((SELECT 'A' + ltrim('    ') + 'B'),'BaseType') as varchar(20))
SELECT CAST(sql_variant_property((SELECT N'A' + ltrim('    ') + 'B'),'BaseType') as varchar(20))
SELECT CAST(sql_variant_property((SELECT 'A' + ltrim('    ') + N'B'),'BaseType') as varchar(20))
SELECT CAST(sql_variant_property((SELECT N'A' + ltrim('    ') + N'B'),'BaseType') as varchar(20))

Upvotes: 1

Related Questions