Ask the Expert

Matching one column value to part of another in SQL

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?

    Requires Free Membership to View

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 first published in May 2007

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: