Remove Extra Whitespace between Words -- But Leave One Space in a Certain Location
-
Dear All, Forgive me for asking what must seem to most like an obvious question, but I am a SQL server newbie. I am using SQL Server 2005 and I have a certain field which contains both a city and a state in one file; i.e., Portland, Oregon will be listed as
CityAndState
PORTLAND OR
Notice that this is a single
VARCHARcolumn and there are a whole bunch of extra spaces in between the'PORTLAND'and'OR'. How do I get it so I haveCityAndState
PORTLAND OR
Sincerely Yours,
Brian Hart -
Dear All, Forgive me for asking what must seem to most like an obvious question, but I am a SQL server newbie. I am using SQL Server 2005 and I have a certain field which contains both a city and a state in one file; i.e., Portland, Oregon will be listed as
CityAndState
PORTLAND OR
Notice that this is a single
VARCHARcolumn and there are a whole bunch of extra spaces in between the'PORTLAND'and'OR'. How do I get it so I haveCityAndState
PORTLAND OR
Sincerely Yours,
Brian Hartyou could do a search for double space, then replace double space by single space, and repeat until nothing gets found. a possible improvement would look for 8 spaces and replace them by 1; then 4 by 1; then 2 by 1. However, it is simply wrong to store two pieces of data in a single field, you really should split them permanently and hence avoid all future formatting issues. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.
-
Dear All, Forgive me for asking what must seem to most like an obvious question, but I am a SQL server newbie. I am using SQL Server 2005 and I have a certain field which contains both a city and a state in one file; i.e., Portland, Oregon will be listed as
CityAndState
PORTLAND OR
Notice that this is a single
VARCHARcolumn and there are a whole bunch of extra spaces in between the'PORTLAND'and'OR'. How do I get it so I haveCityAndState
PORTLAND OR
Sincerely Yours,
Brian Hart -
you could do a search for double space, then replace double space by single space, and repeat until nothing gets found. a possible improvement would look for 8 spaces and replace them by 1; then 4 by 1; then 2 by 1. However, it is simply wrong to store two pieces of data in a single field, you really should split them permanently and hence avoid all future formatting issues. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.
-
Dear All, Forgive me for asking what must seem to most like an obvious question, but I am a SQL server newbie. I am using SQL Server 2005 and I have a certain field which contains both a city and a state in one file; i.e., Portland, Oregon will be listed as
CityAndState
PORTLAND OR
Notice that this is a single
VARCHARcolumn and there are a whole bunch of extra spaces in between the'PORTLAND'and'OR'. How do I get it so I haveCityAndState
PORTLAND OR
Sincerely Yours,
Brian HartTurn it into two columns and then you can format it however you want DECLARE @city varchar(20) Set @city= 'Portland OR' select SUBSTRING(@city, 1,CHARINDEX(' ', @city)) as [city], REVERSE((SUBSTRING(REVERSE(@city),1,CHARINDEX(' ', REVERSE(@city))))) as [state] This assumes no trailing spaces after OR. If there are add some TRIM() in there.
-
Dear All, Forgive me for asking what must seem to most like an obvious question, but I am a SQL server newbie. I am using SQL Server 2005 and I have a certain field which contains both a city and a state in one file; i.e., Portland, Oregon will be listed as
CityAndState
PORTLAND OR
Notice that this is a single
VARCHARcolumn and there are a whole bunch of extra spaces in between the'PORTLAND'and'OR'. How do I get it so I haveCityAndState
PORTLAND OR
Sincerely Yours,
Brian HartSELECT REPLACE(CityAndState,' ','');
Code just replaces DualSpace with NoSpace.
Regards, Hiren. "The more we give of anything, the more we shall get back." - Grace Speare (you can consider this quote while giving vote also) Microsoft Dynamics CRM
-
SELECT REPLACE(CityAndState,' ','');
Code just replaces DualSpace with NoSpace.
Regards, Hiren. "The more we give of anything, the more we shall get back." - Grace Speare (you can consider this quote while giving vote also) Microsoft Dynamics CRM
This is no good if there are an even number of spaces between words.
-
I would be intrigued to know why this answer (above) has been down-voted so much. I think it links to a good solution.
