How does RTRIM work in this query mentioned below

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Data Transformation Services (DTS) » How does RTRIM work in this query mentioned...

How does RTRIM work in this query mentioned below

Rate Topic

Display Mode

Topic Options

Author

Message

anup.sharmaformail

Posted Wednesday, January 28, 2009 10:39 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, February 03, 2009 5:25 AM
Points: 2, Visits: 6

Can someone please help me explain whay inspite of having RTRIM function the below mentioned queries give different results :-- RTEIM trims trainling space ,so the result in both cases should be same.

USE AdventureWorks;
GO
CREATE PROCEDURE FindEmployee @EmpLName char(20)
AS
SELECT @EmpLName = RTRIM(@EmpLName) + '%';
SELECT c.FirstName, c.LastName, a.City
FROM Person.Contact c JOIN Person.Address a ON c.ContactID = a.AddressID
WHERE c.LastName LIKE @EmpLName;
GO
EXEC FindEmployee @EmpLName = 'Barb';
GO

In the FindEmployee procedure, no rows are returned because the char variable (@EmpLName) contains trailing blanks whenever the name contains fewer than 20 characters. Because the LastName column is varchar, there are no trailing blanks. This procedure fails because the trailing blanks are significant.

However, the following example succeeds because trailing blanks are not added to a varchar variable.

Copy Code
USE AdventureWorks;
GO
CREATE PROCEDURE FindEmployee @EmpLName varchar(20)
AS
SELECT @EmpLName = RTRIM(@EmpLName) + '%';
SELECT c.FirstName, c.LastName, a.City
FROM Person.Contact c JOIN Person.Address a ON c.ContactID = a.AddressID
WHERE c.LastName LIKE @EmpLName;
GO
EXEC FindEmployee @EmpLName = 'Barb';

Here is the result set.

Copy Code
FirstName LastName City
---------- ----------------------------------------
Angela Barbariol Snohomish
David Barber Snohomish

(2 row(s) affected)

Post #645129

pss

Posted Wednesday, January 28, 2009 11:28 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 7:21 PM
Points: 107, Visits: 155

I'm not completely sure why the "char" parameter isn't working. The table field "LastName" is nvarchar(50). A "char" can be implicitly converted to an "nvarchar".

But converting your "char" parameter to an "nvarchar" first will solve your problem:

CREATE PROCEDURE FindEmployee @EmpLName char(20)
AS

DECLARE @LName nvarchar(20)

SET @LName = RTRIM(@EmpLName) + '%'

--SELECT @EmpLName = RTRIM(@EmpLName) + '%';
SELECT c.FirstName, c.LastName, a.City
FROM Person.Contact c JOIN Person.Address a ON c.ContactID = a.AddressID
WHERE c.LastName LIKE @LName --@EmpLName;
GO

EXEC FindEmployee @EmpLName = 'Barb';

GO

Post #645182

Lowell

Posted Wednesday, January 28, 2009 11:28 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 11,605, Visits: 27,645

yep when using a varchar for LIKE statemnts, the spaces are significant. a varchar contaning spaces at the end maintains the spaces; it's when you look for equal to a name that the comparison removes the trailing spaces:


DECLARE @table TABLE (FirstName   VARCHAR(30),
                     LastName    VARCHAR(30),
                     City        VARCHAR(30) )
INSERT INTO @table
  SELECT 'Angela     ','Barbariol    ','Snohomish    ' UNION ALL
  SELECT 'David     ','Barber     ','Snohomish    ' UNION ALL
  SELECT CONVERT(CHAR(15),'Angela     '),CONVERT(CHAR(15),'Barbariol    '),CONVERT(CHAR(15),'Snohomish    ') UNION ALL
  SELECT CONVERT(CHAR(30),'David     '),CONVERT(CHAR(30),'Barber     '),CONVERT(CHAR(30),'Snohomish    ') UNION ALL
  SELECT CONVERT(VARCHAR(15),'Angela     '),CONVERT(VARCHAR(15),'Barbariol    '),CONVERT(VARCHAR(15),'Snohomish    ') UNION ALL
  SELECT CONVERT(VARCHAR(30),'David     '),CONVERT(VARCHAR(30),'Barber     '),CONVERT(VARCHAR(30),'Snohomish    ') UNION ALL
  SELECT RTRIM('Angela     '),RTRIM('Barbariol    '),RTRIM('Snohomish    ') UNION ALL
  SELECT RTRIM('David     '),RTRIM('Barber     '),RTRIM('Snohomish    ') 

SELECT '>' + FirstName + '<' AS FirstName,
'>' + LastName + '<' AS LastName,
'>' + City + '<' AS City
FROM @table

SELECT * FROM @table WHERE LastName LIKE 'Barb%'
--fat fingered a trailing space for my LIKE statemnt: no results.
SELECT * FROM @table WHERE LastName LIKE 'Barb %'
SELECT * FROM @table WHERE LastName ='Barber'
SELECT * FROM @table WHERE LastName ='Barber     '

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #645183

anup.sharmaformail

Posted Thursday, January 29, 2009 7:26 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, February 03, 2009 5:25 AM
Points: 2, Visits: 6

Thanks guys for you answers.I appreciate that
, but my question remains unanswered.
The job of RTRIM is to trim spaces.
so how does it matter whether we use CHAR or VARCHAR because finally there wont be any spaces.

Post #645903

« Prev Topic | Next Topic »

Permissions