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 |
|
peryan77
Starting Member
8 Posts |
 Posted - 2006-01-20 : 10:55:19
|
| I have to remove all "accounts" beginning with 00, 01, or 02 from the "allowedAccounts" column. However, multiple accounts exists for one record (comma delimited field). Please note I did not create this database and I am currently in the process of denormalizing, but first I need a query to do this since we are keeping the current system for 2 months or so. example of one record.id allowedAccounts--- ----------------001 00_Report234, 01_Report393, 03_Report59Can someone show me an example of how to do this?Thanks. |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2006-01-20 : 11:44:51
|
| Lookup string manipulation functions in Books Online (BOL).In particular: CHARINDEX, PATINDEX, REVERSE, SUBSTRING, LEFT and RIGHTGood luck! |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-20 : 12:17:03
|
| Use a splitter function, delete the offending rows from the temporary output, re-concatenate the rows that remain, and then Update them back into the table?Sounds pretty horrid as I describe it, but if you are comfortable with Split etc. then I reckon its pretty straightforward.(My thinking is that any sort of in-situ string handling is going to be prone to hacking off something you didn't intend because of edge conditions)Kristen |
|
|
|
|
|
|
|
|
removing string from comma delim. field