September 5, 2012 at 8:07 am
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
September 5, 2012 at 8:38 am
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: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.
September 5, 2012 at 8:45 am
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
September 5, 2012 at 8:48 am
Please could you post your table definition, as I don't know what your table looks like. Thanks.
---
Note to developers: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.
September 5, 2012 at 8:54 am
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: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.
September 5, 2012 at 8:54 am
Table name is state and columns are state_code and State
state_code State
1
September 5, 2012 at 8:56 am
hydbadrose (9/5/2012)
Table name is state and columns are state_code and Statestate_code State
1
This doesn't tell us anything. Please post the CREATE TABLE statement for the table.
September 5, 2012 at 8:56 am
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)
September 5, 2012 at 8:59 am
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: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.
September 5, 2012 at 9:01 am
create table state
(
state_Code int null,
state varchar(50) not null
primary key (state
);
September 5, 2012 at 9:03 am
I already used this update command but did not work. its emptying out the column.
UPDATE [state]
SET [state] = RTRIM([state])
September 5, 2012 at 9:09 am
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).
September 5, 2012 at 9:09 am
Did you use this update command on SQL Server 2008/ and R2?
UPDATE [state]
SET [state] = RTRIM([state])
September 5, 2012 at 9:14 am
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/
September 5, 2012 at 9:14 am
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?
Viewing 15 posts - 1 through 15 (of 45 total)
You must be logged in to reply to this topic. Login to reply