Problem solve
Get help with specific problems with your technologies, process and projects.
Matching one column value to part of another in SQL
How do I pull out all the rows where the description column does not start with its corresponding ID?
I have a table with values such as these:
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
Step 2 of 2:
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>
