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?

r937.com

I have a table with values such as these:

Step 2 of 2:

You forgot to provide an Email Address.

This email address doesn’t appear to be valid.

This email address is already registered. Please login.

You have exceeded the maximum character limit.

Please provide a Corporate E-mail Address.
- I agree to TechTarget’s Terms of Use, Privacy Policy, and the transfer of my information to the United States for processing to provide me with relevant information as described in our Privacy Policy.
Please check the box if you want to proceed.
- I agree to my information being processed by TechTarget and its Partners to contact me via phone, email, or other means regarding information relevant to my professional interests. I may unsubscribe at any time.
Please check the box if you want to proceed.

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

Related Q&A from Rudy Limeback

How to use an SQL CASE expression

Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading

Using the SQL GROUP BY clause for counting combinations

Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading

How to check SQL query construction with the Mimer Validator

Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from 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.

Meet all of our Oracle Database / Applications experts

View all Oracle Database / Applications questions and answers

SearchDataManagement

Facebook alumni forge own paths to big data analytics tools

Startups Interana and Rockset differ in their approaches to providing new query capabilities on fast-arriving big data. Both are ...

Building a business glossary enhances data governance

Experts say data professionals are responsible for establishing a common vocabulary across the organization to help ensure data ...

Why data silos matter: Settling ownership of data issues

Data ownership is still seen as a task for IT -- but that can lead to data silos full of redundant information. Find out why you ...

SearchBusinessAnalytics

Tableau-VoiceBase deal brings speech analytics data to users

Tableau's partnership with AI speech analytics vendor VoiceBase enables users both inside and outside of the contact center to ...

NYPD's Patternizr crime analysis tool raises AI bias concerns

The NYPD has rolled out Patternizr, a machine learning algorithm that helps analysts identify crime patterns but highlights the ...

Users find data preparation tools vital to BI strategies

Data preparation isn't the sexiest topic, but it's critically important to IT and business users, according to a study by Dresner...

SearchSAP

SAP S/4HANA migration still just a plan for most customers

According to a new survey by ASUG, most SAP users plan to move to S/4HANA eventually, but they're currently focused on other IT ...

SAP's vision for full SuccessFactors HCM-Qualtrics integration could take years

SuccessFactors HCM customers have access to workplace data that gives them a sense for what's happening, such as an employee exit...

SAP CEO talks Qualtrics acquisition, says experience data is critical

At the Qualtrics X4 Summit, SAP CEO Bill McDermott argues that experience data is now central to business success. He promised to...

SearchSQLServer

SQL Server auditing best practices: 3 key questions for DBAs

Acing a SQL Server database audit starts with careful monitoring of how sensitive data is accessed and used so you can answer the...

Talend Stitch tools load data to Azure SQL Data Warehouse

Cloud data warehouses see serious data transformation work moving to the cloud. Talend's first integration with Stitch, which it ...

5 trends for SQL Server environments as SQL Server 2019 looms

SQL Server is undergoing new changes, as Microsoft prepares to release the 2019 version of the database software. Other changes ...

TheServerSide.com

Incorporate telemetry to keep a DevOps tools list in check

Don't let a long list of DevOps tools burden your enterprise. Incorporate telemetry to simplify your tooling and prevent ...

Oracle's Java 12 adds developer productivity features

The latest version of the Java Platform, Standard Edition, delivers new features to make developers more productive with the Java...

Find the right model for developing AI applications

Developing AI apps isn't a walk in the park. Here's a guideline on how it differs from traditional application development and ...

SearchDataCenter

Pick the right colocation site for your organization

Colocation is more than signing a contract and installing hardware. Evaluate redundancy, rack space real estate and distance to ...

Can IT admins benefit from a DevOps environment?

Admins might wonder if they need to pay attention to DevOps, but collaboration with developers helps minimize bad code and ...

4 PowerShell modules every IT pro should know

Find out how to use four of the most popular PowerShell community modules in the PowerShell Gallery to help better manage your ...

SearchContentManagement

4 Office 365 collaboration tools that mirror SharePoint

In order to convince users to move away from SharePoint, Microsoft has built Office 365 collaboration tools that improve upon the...

9 killer use cases for CMS blockchain

Uses for blockchain technology in content management and social media exist largely in the realm of the theoretical. But there's ...

Content services and archiving records: It's complicated

Content services complicate archiving. Microservices render records dynamically for many devices, while an archive should be a ...

SearchHRSoftware

HR chatbot limitations include immaturity, weak implementation

Chatbots have the potential to lighten the load on HR professionals and employees for routine internal communications. But ...

AI improves the HR hiring process for recruiters, candidates

Companies can benefit from using AI in the hiring process to target applicants that an ATS might overlook or to direct candidates...

3 types of AI recruiting tools HR can use to find top talent

Forward-thinking recruiters are using AI to improve the hiring process, with tools that streamline screening and give candidates ...

Dig Deeper on Oracle and SQL

Related Q&A from Rudy Limeback

Have a question for an expert?

Start the conversation

0 comments

Register

Login

Forgot your password?

Your password has been sent to:

Please create a username to comment.