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 |
|
scelamko
Constraint Violating Yak Guru
309 Posts |
 Posted - 2005-08-15 : 15:23:05
|
| GuysI have the data in the following format which I need to extract valid data from.ID NAME______________________________________1 TEMPLE INLAND MORTGAGE CORP2 ARVIDA TEXAS HOMES L/P 601 3 TWIN CREEKS PARTNERS L/P 300 4 DENTON COUNTY ELECTRIC C5 AMERICAN SAVINGS BANK 400D 6 AMERITEX RESIDENTIAL MORTGAGE7 AMERITEX RESIDENTIAL MTG 117 The length of the NAME column is (30), In some cases though the data is not clean.For Example:For ID = 2 the NAME column has ' 601' which needs to removed and for ID = 6 the data is clean.I am not able to write any generic script using substring and charindex functions that will eliminate the spaces and numeric data at the end of it and at the same time keep the clean data (for eg: id = 6).Any ideas, suggestions???Thanks |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-08-15 : 16:29:46
|
| select left(name,len(name) - patindex('%[^1-9 ]%', reverse(name))+1)from tblWon't handle 5 but you'll have to give a rule that would exclude 400D but allow whatever is really in the name.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
Data Extraction