How to remove blank space of a string in SQL server

Question

1.00/5 (1 vote)

See more:

SQL-Server-2012

In my table having a column named Remarks there is a blank space at the end. I used LTRIM,RTRIM & Replace function. It's not woking. Suggest any other ways to remove the blank space.

What I have tried:

update TableName set Remarks= LTRIM(RTRIM(Remarks)) 
update TableName set Remarks= replace(Remarks,' ','')

Posted 10-Jun-18 23:03pm

kirthiga S

Updated 10-Jun-18 23:13pm

Add a Solution

Comments

Maciej Los 11-Jun-18 5:05am

Are you sure it's a space? It might be a carriage return or end of line sign.

kirthiga S 11-Jun-18 5:07am

It is a space

Andy Lanng 11-Jun-18 5:16am

Both of your solutions are correct. Try looking at the char value. Isolate the space char in the string you have in your example and try ASCII(theChar). You can use that value in your replace "Replace(Char(32),'')" for example

kirthiga S 11-Jun-18 5:24am

Ya It's working

Jochen Arndt 11-Jun-18 5:19am

The update statement should work.

How do you checked that there is a space?
Maybe it is not in the database but in the frontend showing the field contents.
Or it is not an ASCII space (code 32 / 0x20) but one of the various Unicode spaces.

BTW: Use TRIM() if you want to remove leading and trailing spaces.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2018-06-10T23:15:00

As i mentioned in the comment to the question, it might be a carriage return or end of line sign.

The only way to find out what kind of char is at the end of string, is to use ASCII function, which return the ASCII code value.

SQL

SELECT ASCII(RIGHT(Remarks, 1)) AS LastCharAsciiCode
FROM TableName

Now, when you know what kind of char is there, you can update your table!

Or

You can replace all unwanted chars

update TableName set Remarks= REPLACE(REPLACE(REPLACE(Remarks,' ',''), CHR(10), ''), CHR(13), '')

Good luck!