ltrim & rtrim returning null 1

[kaht]

when I run the following command:

ltrim(rtrim('')) I am getting a value of NULL returned.

up until earlier today this command has always returned ''

is there a setting that can be changed that will cause this command to return different values?

the odd thing is that if I change to a different database and run the command I get the empty string as expected, so I expect that there has been some setting that has been switched for this particular database


thanks in advance

-kaht

[small]How spicy would you like your chang sauce? Oh man... I have no idea what's goin' on right now...[/small]

 

[SQLBill]

Are you and Will192 the same person? Or do you work together?

THREAD183-1234179

-SQLBill

Posting advice: FAQ481-4875

 

[kaht]

Yeah, we work together. When I said that I'd post to the forum about this question I didn't think that he'd post to the same one....

Anyway, the problem is fixed - a compatibility setting of 65 caused the command to return null, switching it to 80 (like all the other databases) fixed the situation.


Now.... to find out how in the world it got set to 65 in the first place......

-kaht

[small]How spicy would you like your chang sauce? Oh man... I have no idea what's goin' on right now...[/small]

 

[SQLDenis]

run sp_helpdb

then look at the compatibility leve, i suspect the one with NULL is set to 6.5

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

Yep,

SELECT LTRIM(RTRIM(''))

EXEC sp_dbcmptlevel 'pubs', 60

SELECT LTRIM(RTRIM(''))

EXEC sp_dbcmptlevel 'pubs', 80

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Will192]

I fixed it. Somehow the compatibility level got set to 65 and my recovery model got changed also. Not sure how this happened and I can't find anything in the logs to see when it happened.

Thanks for the help.

 

[SQLBill]

Is this a database that you created? Or is it a vendor's database?

We had a vendor created database where the vendor supplied an update and without informing anyone they decided their update would set the database to the SIMPLE mode. It didn't change the compatibility level, but it did change the recovery mode.

-SQLBill

Posting advice: FAQ481-4875

 

[kaht]

Thanks for the tips guys, will isn't gonna leave any stars I'm sure so have a pair on me.

-kaht

[small]How spicy would you like your chang sauce? Oh man... I have no idea what's goin' on right now...[/small]

 

[SQLDenis]

tx

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/