I have a table with values such as these:
GLAccountID GLAccountDesc 7100 7512 - LINEN DISPOSABLES 7143 7141 - DISPOSABLE SUPPLIES 7102 7100 - GENERAL MEDICAL SUPPLIES 7102 7102 - IV TUBING 7104 7104 - SUTURES 7130 7130 - IMPLANTS - FIXATION 7145 7145 - DISPOSABLE SUPPLIES
In this table, GLAccountDesc value should always start with its corresponding GLAccountID, a hyphen and then the description. How do I pull out all the rows where GLAccountDesc does not start with its corresponding GLAccountID?
This seems fairly straightforward:
select GLAccountID , GLAccountDesc from daTable where GLAccountID <> substring(GLAccountDesc from 1 for 4)
Presumably it isn't quite so obvious if you have never used the SUBSTRING function in a comparison before.
To me, a more interesting solution is this one:
update daTable set GLAccountDesc = substring(GLAccountDesc from 5)
Much more satisfying, don't you think? <wink>
This was first published in May 2007