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:
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 last published in May 2007
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.
Meet all of our Oracle Database / Applications experts
View all Oracle Database / Applications questions and answers
Start the conversation
0 comments