how to remove white space in sql

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hi guys,

i have a table in sql,

where in the columns while inserting it has taken white at left of every column.

so, i have tried ltrim(rtrim(newref)) but it is not removing the white space.

even, i have tried this as well,

ltrim(left(RTRIM(replace(New_Ref, char(160), char(32))),3)

but still its not triming...

Can anyone please help me.

thanks

Posted 26-Nov-14 5:23am

abdul subhan mohammed

Add a Solution

Comments

DamithSL 26-Nov-14 11:28am

can you update the question with your insert code?
are you trying to select data or update?

jaket-cp 26-Nov-14 11:31am

maybe it is not a space
try something like select ascii(substring(ColumnNm, 1, 1)) from theTable; to see what character it is.

abdul subhan mohammed 26-Nov-14 11:35am

this updated col value to 9 in each row... instead of col value

jaket-cp 26-Nov-14 12:08pm

not sure what you mean.
Ah got you, yes Char(9) is a tab, so replace tab with empty string.

abdul subhan mohammed 26-Nov-14 11:31am

actually i have uploaded data from excel, because of that i got white space.

jaket-cp 26-Nov-14 12:09pm

if you uploaded from excel then it may be a tab and not a space.
Try and replace the tab ' ' with empty string ''.

Maciej Los 26-Nov-14 12:00pm

Post sample input data.

3 solutions

Solution 2

Ascii values from 32 to 126 contains Numbers[0-9],Alphabet[A-Z/a-z] and acceptable special characters.

So you can check each character of your string to have this ascii value and if not then remove that character.

You can create a function of below code and use it to check each row of your table

SQL

Declare @Str varchar(50)='Hi you …there'
DECLARE @Result nvarchar(max)
    SET @Result = ''
 
    DECLARE @char nvarchar(1)
    DECLARE @charpos int
 
    SET @charpos = 1
    WHILE @charpos <= LEN(@Str)
    BEGIN
        SET @char = SUBSTRING(@Str , @charpos, 1)
 
        IF ASCII(@char) >=32 and ASCII(@char) <=126
            SET @Result = @Result + @char
        SET @charpos = @charpos + 1
    END
 
    select @Result

Posted 26-Nov-14 6:05am

Shweta N Mishra

Solution 1

you might find this link useful sql-trim

Posted 26-Nov-14 5:55am

Asim Ghazanfar

Updated 26-Nov-14 6:07am

v3

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)

jaket-cp · Accepted Answer · 2014-11-26T06:25:00

From the comments it appears as though tabs have been inserted and not spaces.
To remove them you could do something like this:

SQL

--set up test data
declare @t table(id int identity(1,1),col varchar(50));
insert into @t 
select '	hello' col union all
select '	world' col union all
select 'hello world' col union all
select '	hello' col union all
select 'world' col union all
select '	hello world' col union all
select '	world hello' col

--remove tab from start of string
update @t 
set col = substring(col, 2, len(col))
where substring(col, 1, 1) = char(9) ;