How does RTRIM work in this query mentioned below

Question

How does RTRIM work in this query mentioned below

anup.sharmaformail

SSC Enthusiast

Points: 124
More actions
January 28, 2009 at 10:39 am

#77181

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)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

pss SSC-Addicted Points: 445 More actions · Answer 1

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

Lowell SSC Guru Points: 323518 More actions · Answer 2

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:

[font="Courier New"]

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 '[/font]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

anup.sharmaformail SSC Enthusiast Points: 124 More actions · Answer 3

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.