Please Help on Trailing spaces

Question

Post reply

Please Help on Trailing spaces

hydbadrose

SSCarpal Tunnel

Points: 4164
More actions
September 5, 2012 at 8:07 am

#267263

Hello,
Can someone please help me removing the trailing spaces, leading spaces, white space, tabs, carriage returns, line feeds etc. for example I have a state column and the values are as;
Ohio
Virginia
Chicago
Florida

Viewing 15 posts - 1 through 15 (of 45 total)

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

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 1

One solution for leading and trailing spaces is to use the LTRIM / RTRIM functions i.e.

SELECT LTRIM(RTRIM(state)) FROM dbo.states

However I've read before that this can lead to performance issues, since these functions have an impact on how quickly the query completes.

I reckon that using string functions would also cause havoc with the query optimiser too, in the way it uses indexes, but I may be totally wrong.

You can also use other string functions like STUFF and REPLACE if you're targeting particular characters. I.e. line breaks could be targeted and replaced by using REPLACE(state,(CHAR(13) + CHAR(10)),'').

You can combine string functions, so for example to remove leading and trailing spaces AND line breaks, you could use:

SELECT REPLACE( LTRIM(RTRIM(state)),(CHAR(13) + CHAR(10)),'') FROM dbo.states

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

hydbadrose SSCarpal Tunnel Points: 4164 More actions · Answer 2

Thank you so much for the reply I will try to use the script. Can you also please send me the update script to update the state column to remove the trailing spaces? Thanks

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 3

Please could you post your table definition, as I don't know what your table looks like. Thanks.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 4

Just tested my hypothesis that string functions take significantly longer to parse, and can't see a significant difference.

10,000 rows of non-unique data with repeating patterns of ' ' before and after the name, and multiple line breaks (VARCHAR(100) )

vs.

10,000 rows of trimmed and tidy data.

SELECT REPLACE( LTRIM(RTRIM(state)),(CHAR(13) + CHAR(10)),'') FROM dbo.states

yielded approx 48ms for a result set.

SELECT state FROM dbo.state

yielded approx 47ms for a result set.

I don't have time to test for larger result sets or with different parameters, unfortunately.

But it shows my earlier comments about string manipulation may not be accurate, so please disregard.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

hydbadrose SSCarpal Tunnel Points: 4164 More actions · Answer 5

Table name is state and columns are state_code and State

state_code State

1

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

hydbadrose (9/5/2012)
Table name is state and columns are state_code and State
state_code State
1

This doesn't tell us anything. Please post the CREATE TABLE statement for the table.

hydbadrose SSCarpal Tunnel Points: 4164 More actions · Answer 7

Sorry I clicked on post accidently-

State_code State

1 Virginia

2 Maryland ( whitepsace before Maryland)

3 West Virginia ( again white space before the state name)

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 8

Test on dev first please.

For trailing spaces only:

UPDATE [state]

SET [state] = RTRIM([state])

By the way, the name 'state' is an awful word to use for a table name, as it's reserved.

I've put the [square brackets] around it to differentiate.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

hydbadrose SSCarpal Tunnel Points: 4164 More actions · Answer 9

create table state

(

state_Code int null,

state varchar(50) not null

primary key (state

);

hydbadrose SSCarpal Tunnel Points: 4164 More actions · Answer 10

I already used this update command but did not work. its emptying out the column.

UPDATE [state]

SET [state] = RTRIM([state])

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 11

hydbadrose (9/5/2012)
I already used this update command but did not work. its emptying out the column.
UPDATE [state]
SET [state] = RTRIM([state])

The above command cannot "empty out" the column. You did something else to clean it!

RTRIM is in-build T-SQL function which removes trailing spaces (there is another one: LTRIM, which removes leading spaces).

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

hydbadrose SSCarpal Tunnel Points: 4164 More actions · Answer 12

Did you use this update command on SQL Server 2008/ and R2?

UPDATE [state]

SET [state] = RTRIM([state])

Sean Lange SSC Guru Points: 286590 More actions · Answer 13

hydbadrose (9/5/2012)
create table state
(
state_Code int null,
state varchar(50) not null
primary key (state
);

????

You really need to try your scripts before you post them.

I would also recommend in a state table that you have the actual abbreviation in there. VA, MD, WV, etc..

The reason that Derek said the name "state" is awful is because you should avoid sql keywords in object names. You have also now created a column by the same name.

select state from state??? Or is that select state.state from state???

A more generally accepted format for a States table is something like the following:

create table #States

(

ST char(2) primary key,

StateName varchar(25)

)

insert #states

select 'VA', 'Virginia' union all

select 'MD', 'Maryland' union all

select 'WV', 'West Virginia'

select * from #States

As for your update you were given almost the entire code.

The basic syntax for an update is:

Update table set column = value

All you need to do is get the value which was given to you previously.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

hydbadrose (9/5/2012)
Did you use this update command on SQL Server 2008/ and R2?
UPDATE [state]
SET [state] = RTRIM([state])

Are you saying that this UPDATE statement deleted data?