Q

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

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close