LearnItFirst User Forum

Welcome Guest

Search | New Posts | Members | Log In | Register

SQL Server Forum - LearnItFirst.com » SQL Server Database Administration » General SQL Database Question & Answer » Steps to remove LTRIM and RTRIM

Steps to remove LTRIM and RTRIM

Options

Previous Topic · Next Topic

manzoor_mustafa

Posted: Monday, September 07, 2009 5:24:56 AM

Rank: Newbie

Joined: 9/7/2009
Posts: 1
Points: 3
Where do you live?: India

Hi

For DB migration to SQL SERVER 2008, i need to know how to handle the incompactibilty of "LTRIM" function. In SQL SERVER 6.5, LTRIM('') return NULL where as in 2008, EMPTY STRING is returned (from SQL SERVER 2000 onwards, EMPTY STRING is returned).

This had been already documented in "sp_dbcmptlevel" topic:
http://msdn.microsoft.com/en-us/library/aa259649(SQL.80).aspx

Curently, i am manually looking into all objects where LTRIM or RTRIM are used and adding a condition to return NULL(existing business logic checks for NULL value to perform some action at DB level itself).

Is there any work around or a better approach to address this issue(as there more than 800 DB objects - SPs, User Functions to be checked)?

Thanks

Scott Whigham

Posted: Monday, September 07, 2009 10:57:18 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 476
Points: 1,053
Where do you live?: Dallas, TX

Wow - that is unfortunate! I don't know of another way other than manually modifying the SPs/code unfortunately.

I would advise you to look into using the NULLIF() function instead of long CASE operators for this though.

Code:

SELECT ColumnContainingNulls
, CASE WHEN LEN(LTRIM(RTRIM(ColumnContainingNulls))) = 0 THEN NULL ELSE LTRIM(RTRIM(ColumnContainingNulls)) END
, NULLIF(LTRIM(RTRIM(ColumnContainingNulls)), '')
FROM dbo.MyTable

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.