Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ChrisHorsley
Starting Member
7 Posts |
 Posted - 2004-09-07 : 14:52:16
|
| I have a table of addresses I need to import each month and under the 'County' Column the word County or Burrough follows each county name. Ex 'Baltimore County', 'Washington County', 'Davis Burrough' etc.Using DTS is there a way to remove the space and County(Burrough) following each county name? I've tried using the trim feature but that only seems the Trim the White Space.Thanks for any help you can offer. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-07 : 14:58:54
|
| Import the data into a staging table. Then use SUBSTRING with PATINDEX to move the data from staging table to your table using INSERT INTO.Tara |
 |
|
|
ChrisHorsley
Starting Member
7 Posts |
Posted - 2004-09-07 : 15:20:24
|
| Could I trouble you for a little code to get me started? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-07 : 15:25:47
|
| Use PATINDEX to find the location of the space. Then use SUBSTRING to get the characters up to the space. So you'll have something like this:INSERT INTO...SELECT SUBSTRING(Column1, 1, PATINDEX('% %', Column1) - 1)FROM StagingTableI did not test this though, so you'll need to do that on your own. Look up SUBSTRING and PATINDEX for more information if you don't understand the code that I posted.Tara |
|
|
|
ChrisHorsley
Starting Member
7 Posts |
Posted - 2004-09-07 : 16:38:34
|
| You're a star. I'll give it a try.Thanks |
|
|
|
ChrisHorsley
Starting Member
7 Posts |
Posted - 2004-09-08 : 09:09:04
|
| Thanks for the help Tara, I used the following code successfully:UPDATE TableNameSET County = (REPLACE(REPLACE(County,' County',''),' Borough',''))GO |
|
|
|
|
|
|
Use DTS to trim white space and text...