I have a table with values such as these:
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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>
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.continue reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clausecontinue reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.