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>
Dig deeper on Oracle and SQL
Related Q&A from Rudy Limeback, SQL Consultant, r937.com
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.