Monday, October 11, 2010

Ltrim Rtrim not working

In Sql server when you find ltrim and rtrim is not working.Then it must be some ascii characters added to it.
You can find the ascii characters

DECLARE @Val VARCHAR(50)
DECLARE @i INT

SET @Val = 'Pastehere '
SET @i = 0

WHILE @i <= DATALENGTH(@Val)
BEGIN
PRINT SUBSTRING(@Val, @i, 1) + ' : ' + CAST(ASCII(SUBSTRING(@Val, @i, 1)) AS VARCHAR(3))
SET @i = @i + 1
END

Once you find the ascii characters replace it with space '' or char(32)
update REPLACE(City,CHAR(160),''),City from tblAddress where id=140
Then
Trim it
update ltrim(rtrim(City))from tblAddress where id=140

No comments:

Post a Comment