Trim Functions Not Working Properly
-
I imported an Excel spreadsheet into the database in its own table. I changed to column data type to varchar(255) and I am trying to use rtrim and ltrim to trim off the spaces before and after the names, but it is not working....does anyone know why and/or have any advice on how to remedy this problem?
Thanks,
Michael
-
See if this gives better results. You probably have whitespace characters other than space.
- select
rtrim(ltrim(replace(replace(replace(colname,char(9),' '),char(10),' '),char(13),' ')))
- from
yourtable
I hate Excel. I have seen it go though a column of dates and interpret those with day <= 12 as if they were in default format, and those with day > 12 (i.e. couldnt be treated as in default format) as though they were in the alternative format.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Hi Michel,
I am using the following Query : but it is executing properly.
select ltrim(rtrim(convert(varchar(255),
' gfbdfrfrf dfjdfhjdfh kjfdkf ')))
Regards,
Amit Gupta
-
Amit, how does that help?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
It did not work...unfortunately. I have no idea what is going on...but I am getting angry quick...I guess I will try substrings...
Thanks anyway
-
can you post results of:
select distinct ascii(substring(yourcol,1,1))
from yourtable
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
160
-
That's the ASCII for a blank that we often use when we don't want spaces trimmed for various reasons. Try:
SELECT Replace(colname, Char(160),'')
...and if you still have problems, then you also have actual spaces, so you can wrap the above with trim functions as needed.
-
I have learned something today!
Thanks folks
-
That Worked for me too.. Thanks a Lot
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply