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 |
|
jcoleman_11
Starting Member
11 Posts |
 Posted - 2010-05-25 : 16:44:59
|
| Hey guys,i need help and am on a deadline. I need to update this table. Basically, one of the values is a code. i need to trim the codes down to only the first 11 characters, but only the codes beginning with 3. so...basically 123456789123456 needs to be 12345678912, but i've got hundreds of these and don't want to update them one by one.update tblscancode [need help here] where scancode like '3%'what goes in the [need help here] spot.Thanks guys! |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-05-25 : 16:46:35
|
| update tblscancode set scancode=left(scancode,11) where scancode like '3%' |
 |
|
|
jcoleman_11
Starting Member
11 Posts |
Posted - 2010-05-25 : 16:59:02
|
| thanks robvolk, that worked wonders!! now my only concern is that i will have duplicates. Whats the easiest way to make sure i only have unique values? |
|
|
|
jcoleman_11
Starting Member
11 Posts |
Posted - 2010-05-25 : 17:02:33
|
| i quickly looked through it, and there don't appear to be any duplicates. Is it possible that dbisqlc knows there are dupes and truncates the extra values? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-05-25 : 17:51:51
|
| You'll have to handle duplicates manually, but you can look for them with the following query:select count(distinct scancode), count(distinct left(scancode,11)) from tblscancode where scancode like '3%'If both counts are the same then you can safely trim off the characters. If they differ you'll have to investigate. |
|
|
|
jcoleman_11
Starting Member
11 Posts |
Posted - 2010-05-25 : 19:04:25
|
| awesome, you've been a great help! |
|
|
|
|
|
|
update value to remove all characters more than 11