How to remove blank spaces

Question

Post reply

How to remove blank spaces

jagadish_sds

Ten Centuries

Points: 1042
More actions
December 12, 2008 at 11:38 am

#216407

In Company table column name "CompanyInfo" varchar(750).
In this column company information is stored, but end of this string some blank spaces are added.
I tried to remove blank spaces using RTRIM(CompanyInfo) or REPLACE(CompanyInfo,' ','').
It removed blank spaces, but end of the string one blank space remain same, if I use again RTRIM or REPLACE it won't go.
before trim CompanyInfo='This is the description of company '
after trim CompanyInfo='This is the description of company '
Can anyone please tell me, what is the problem and how to remove blank spaces.
Thanks,
JS

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29423 More actions · Answer 1

Can u check the ascii code of the last character?

select ascii(right(companyinfo,1)) from company

if its 32, it's a space and should be removed using rtrim

if its something else, you need to replace that character using replace ()function

REPLACE(CompanyInfo,CHAR(use the ascii value here),'')

Pradeep Singh

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

You may want to view this page

http://msdn.microsoft.com/en-us/library/218s85f8.aspx

to see a list of non-printable characters

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

jagadish_sds Ten Centuries Points: 1042 More actions · Answer 3

I got an answer after researching.

It was problem with \t,,\r

It used this statement to remove blank spaces.

LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(CompanyInfo, CHAR(13), ''), CHAR(10), ''), CHAR(9), '')))

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 4

Cool... thanks for posting what you ended up with.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

gkarung SSC Enthusiast Points: 105 More actions · Answer 5

Hi All,

How to remove database unallocated space in the server.

After deleted the unwanted tables in the database we got 40gb unallocated space in the drive.

Thanks

G Arunagiri

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 6

gkarung (9/30/2010)
Hi All,
How to remove database unallocated space in the server.
After deleted the unwanted tables in the database we got 40gb unallocated space in the drive.
Thanks
G Arunagiri

It may seem like a silly question but how much allocated space remains in that database?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)