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_aSELECT 'KOLATHUR' ,'332113 'INSERT INTO @tbl_aSELECT 'NELLAYI' , '332113 'INSERT INTO @tbl_aSELECT 'KODAKARA' , '332113 'INSERT INTO @tbl_aSELECT '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" |
|
|
aoriju
Posting Yak Master
156 Posts |
Posted - 2013-04-19 : 02:20:19
|
SQL_Latin1_General_CP1_CI_AS |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
aoriju
Posting Yak Master
156 Posts |
Posted - 2013-04-19 : 02:23:03
|
Visakh, that will retieve only 1 record...i need all |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-19 : 02:38:06
|
nope it will retrieve allsee this illustrationPlease note that there's no space at the end of the constant valueDECLARE @tbl_a AS TABLE(place VARCHAR(100), DIscode VARCHAR(100));INSERT INTO @tbl_aSELECT 'KOLATHUR' ,'332113 'INSERT INTO @tbl_aSELECT 'NELLAYI' , '332113 'INSERT INTO @tbl_aSELECT 'KODAKARA' , '332113 'INSERT INTO @tbl_aSELECT 'CHALAKUDY' , '332113'SELECT * FROM @tbl_a WHERE LTRIM(RTRIM(DIscode)) = '332113'output-----------------------------------------place DIscode--------------------------KOLATHUR 332113 NELLAYI 332113 KODAKARA 332113 CHALAKUDY 332113 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 beSELECT * FROM @tbl_a WHERE REPLACE(DIscode,CHAR(160),'') = '332113'------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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. |
|
|
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_aSELECT 'KOLATHUR' ,'332113 'INSERT INTO @tbl_aSELECT 'NELLAYI' , '332113 'INSERT INTO @tbl_aSELECT 'KODAKARA' , '332113 'INSERT INTO @tbl_aSELECT '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 |
|
|
|