4

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?

2
  • 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? Commented Oct 6, 2022 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´ Commented Oct 6, 2022 at 23:36

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.