Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How does RTRIM work in this query mentioned below Expand / Collapse
Author
Message
Posted Wednesday, January 28, 2009 10:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 3, 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
Posted Wednesday, January 28, 2009 11:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Wednesday, January 28, 2009 11:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:38 AM
Points: 12,910, Visits: 32,021
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
Posted Thursday, January 29, 2009 7:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 3, 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 »

Add to briefcase

Permissions Expand / Collapse