| Author |
Topic |
|
dsteinschneider
Starting Member
2 Posts |
 Posted - 2003-05-28 : 20:33:25
|
| I have a table that contains two columns, an ID field and a field that contains Address,City,State,Zip separated by commas.Example -> 1 Main Street,Anytown,MA,01001 <-I am trying to parse out to individual columns I have added to the table. I have successfully with help from articles here parsed out csv values to another table as separate records for each value. I thought this would be simpler but I've gotten stuck on it. Any help would be greatly appreciatedDoug |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-05-28 : 21:42:22
|
| You might even be able to do this:UPDATE myTable SET Zip=ParseName(Replace(myColumn, ',', '.'),1),State=ParseName(Replace(myColumn, ',', '.'),2),City=ParseName(Replace(myColumn, ',', '.'),3),Street=ParseName(Replace(myColumn, ',', '.'),1)The ParseName() function works on a string that contains periods, used to parse each section of a qualified object name (server.database.owner.object). If your column does not contain any periods in it, then you can replace the commas with a period and use ParseName() to split the columns out. If they DO contain periods, you can use an additional Replace() to replace them with another character, then Replace() that again to change it back to a period. You may end up with some really funky looking SQL, but it'll let you do it in one UPDATE statement:UPDATE myTable SET Zip=Replace(ParseName(Replace(Replace(myColumn,'.','~'), ',', '.'),1), '~', '.'),State=Replace(ParseName(Replace(Replace(myColumn,'.','~'), ',', '.'),2), '~', '.'),City=Replace(ParseName(Replace(Replace(myColumn,'.','~'), ',', '.'),3), '~', '.'),Street=Replace(ParseName(Replace(Replace(myColumn,'.','~'), ',', '.'),4), '~', '.') |
 |
|
|
dsdeming
479 Posts |
Posted - 2003-05-28 : 22:31:14
|
| That is some really funky looking SQL. It never would have occured to me to use ParseName, but it's a clever way to solve this problem. I guess the only limitation here is that because fully-qualified SQL object names only have four parts, you couldn't use ParseName to parse out Address1, Address2, City, State, Zip. At least not without getting really, really funky. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-05-28 : 22:53:05
|
| Yeah, I forgot to mention that ParseName() cannot parse more than 4 parts. Although it can do fewer parts, you'll get errors if a part is missing. You should test it carefully in a SELECT against your real data before you run it in an UPDATE statement. |
|
|
|
dsteinschneider
Starting Member
2 Posts |
Posted - 2003-05-29 : 09:29:20
|
| Hello Rob,That was a very creative solution that works well in my case because the column I'm parsing is consistently 4 parts. I am first replacing periods with empty strings and then filling in missing components with a filler character (There are some foreign addresses that are missing components). If there is no character between to periods (missing address component) then parsename is returns nulls for any of the name parts. Do you think there is SET based way to parse for the 1st, 2nd, 3rd, and 4th instances of a character using other functions?Thanks,Doug |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-05-29 : 19:01:51
|
| You can try modifying the techniques listed here:http://www.sqlteam.com/SearchResults.asp?SearchTerms=csvBut technically, ParseName() is a set based method because it treats the entire table as one set. I don't think any other method will perform as well either. |
|
|
|
|
|
|
Parse column with 4 csv values into their own cols