I noted an (for me) unexpected behavior on ltrim() and rtrim() functions on Sybase ASE T-SQL engines. The weird behavior I observed occurs on ltrim() and rtrim() functions for empty and only-spaces strings arguments; they return NULL. I consider this behavior weird because one (or me at least) expect ltrim() and rtrim() string functions just remove starting and ending spaces; not to turn a not-null value (as empty and only-spaces strings) to NULL
To reproduce the issue, I wrote the script below. Knowing that using of the = operator against null isn't a good practice, I just included those examples for educative purposes
begin
create table #tmpSomeTable (
rowId int not null,
someStringColumn varchar(10) null
)
insert into #tmpSomeTable(someStringColumn,rowId) values (null,1)
insert into #tmpSomeTable(someStringColumn,rowId) values ('',2)
insert into #tmpSomeTable(someStringColumn,rowId) values (' ',3)
insert into #tmpSomeTable(someStringColumn,rowId) values (' ',4)
--
select '=null predicate' as [predicate]
,*
from #tmpSomeTable
where someStringColumn = null
select 'is null predicate' as [predicate]
,*
from #tmpSomeTable
where someStringColumn is null
--ltrim() when evaluated for empty string from rowId=2 row returns null
select '=null predicate' as [predicate]
,*
from #tmpSomeTable
where ltrim(someStringColumn) = null
select 'is null predicate' as [predicate]
,*
from #tmpSomeTable
where ltrim(someStringColumn) is null
--rtrim() when evaluated for empty string from rowId=2 row returns null
select '=null predicate' as [predicate]
,*
from #tmpSomeTable
where rtrim(someStringColumn) = null
select 'is null predicate' as [predicate]
,*
from #tmpSomeTable
where rtrim(someStringColumn) = null
drop table #tmpSomeTable
end
My questions about this are ¿Why ltrim() and rtrim() native functions return null when evaluated for empty and only-spaces strings on Sybase ASE Database Engine? ¿Is that an expected behavior? ¿Is that a non-deterministic behavior defined by database instance parameters? Is that a Bug or Known-issue?
ltrim()/rtrim()of a multi-space string returnsNULL); a quick review of the ASE manuals doesn't show anything specifically addressing this behavior and I'm guessing that's because (most?) people would understand that removing all spaces from a space-only string with leave nothing (ie,NULL); what RDBMS, or programming languages, have you seenltrim()/rtrim()(or the equivalent funtion) return something other thanNULLfor a space-only string?ltrim()andrtrim()for T-SQL on Sql-Server returns empty-string when applied on empty or multi-space strings. On sql-server they only returnnullwhen are evaluated fornullargument. Even more, on sql-server ´=´ operator evaluated againstnullis not ´true´ or neither ´false´