Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 LTRIM & RTRIM not working properly

Author  Topic 

aoriju
Posting Yak Master

156 Posts

Posted - 2013-04-19 : 02:04:47
DECLARE @tbl_a AS TABLE(place VARCHAR(100), DIscode VARCHAR(100));
INSERT INTO @tbl_a
SELECT 'KOLATHUR' ,'332113 '
INSERT INTO @tbl_a
SELECT 'NELLAYI' , '332113  '
INSERT INTO @tbl_a
SELECT 'KODAKARA' , '332113  '
INSERT INTO @tbl_a
SELECT 'CHALAKUDY' , '332113'

SELECT * FROM @tbl_a WHERE DIscode = LTRIM(LTRIM('332113 '))


not getting all records

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-04-19 : 02:12:18
Which collation are you using?



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

aoriju
Posting Yak Master

156 Posts

Posted - 2013-04-19 : 02:20:19
SQL_Latin1_General_CP1_CI_AS
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-19 : 02:20:32
shouldnt it be this?



SELECT * FROM @tbl_a WHERE RTRIM(LTRIM(DIscode)) = '332113'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

aoriju
Posting Yak Master

156 Posts

Posted - 2013-04-19 : 02:23:03
Visakh, that will retieve only 1 record...i need all
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-19 : 02:38:06
nope it will retrieve all
see this illustration
Please note that there's no space at the end of the constant value

DECLARE @tbl_a AS TABLE(place VARCHAR(100), DIscode VARCHAR(100));
INSERT INTO @tbl_a
SELECT 'KOLATHUR' ,'332113 '
INSERT INTO @tbl_a
SELECT 'NELLAYI' , '332113 '
INSERT INTO @tbl_a
SELECT 'KODAKARA' , '332113 '
INSERT INTO @tbl_a
SELECT 'CHALAKUDY' , '332113'

SELECT * FROM @tbl_a WHERE LTRIM(RTRIM(DIscode)) = '332113'


output
-----------------------------------------
place DIscode
--------------------------
KOLATHUR 332113
NELLAYI 332113
KODAKARA 332113
CHALAKUDY 332113



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-19 : 02:40:51
my other guess is it may be a hardspace (char(160)) in which case solution should be

SELECT * FROM @tbl_a WHERE REPLACE(DIscode,CHAR(160),'') = '332113'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-19 : 12:33:16
SQL ignores traiing spaces (in most situations), so something else is going on. As Visakh mentions, it might be a "funky" character.
Go to Top of Page

chadmat
The Chadinator

1974 Posts

Posted - 2013-04-19 : 14:12:48
quote:
Originally posted by aoriju

DECLARE @tbl_a AS TABLE(place VARCHAR(100), DIscode VARCHAR(100));
INSERT INTO @tbl_a
SELECT 'KOLATHUR' ,'332113 '
INSERT INTO @tbl_a
SELECT 'NELLAYI' , '332113  '
INSERT INTO @tbl_a
SELECT 'KODAKARA' , '332113  '
INSERT INTO @tbl_a
SELECT 'CHALAKUDY' , '332113'

SELECT * FROM @tbl_a WHERE DIscode = LTRIM(LTRIM('332113 '))


not getting all records



Not that this is your issue, but you are doing LTRIM...LTRIM, no RTRIM.

I cut and paste your code, and I get all records, even with no rtrim.

-Chad
Go to Top of Page
   

- Advertisement -