2

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?

Mauricio Ortega
  • 301
  • 2
  • 12
  • ASE T-SQL has always behaved this way (ie, `ltrim()/rtrim()` of a multi-space string returns `NULL`); 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 seen `ltrim()/rtrim()` (or the equivalent funtion) return something other than `NULL` for a space-only string? – markp-fuso Oct 06 '22 at 21:07
  • @markp-fuso `ltrim()` and `rtrim()` for T-SQL on Sql-Server returns empty-string when applied on empty or multi-space strings. On sql-server they only return `null` when are evaluated for `null` argument. Even more, on sql-server ´=´ operator evaluated against `null` is not ´true´ or neither ´false´ – Mauricio Ortega Oct 06 '22 at 23:36

0 Answers0