
	Home > Ask the Oracle Experts > SQL Questions & Answers > Matching one column value to part of another in SQL

Ask The Oracle Expert: Questions & Answers

EMAIL THIS

Matching one column value to part of another in SQL

EXPERT RESPONSE FROM: Rudy Limeback

		Pose a Question

		Other Oracle Categories

		Meet all Oracle Experts
		Become an Expert for this site

Oracle tips, scripts, and expert advice

Digg This! StumbleUpon Del.icio.us

QUESTION POSED ON: 24 May 2007
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?

EXPERT RESPONSE

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>

Digg This!

StumbleUpon

Del.icio.us

RELATED CONTENT

	SQL
	How to return a zero in SQL instead of no row back for a select count
	How to write an SQL query using GROUP BY for row analysis
	Using nested SQL string functions to find ERP customer values in a table
	SQL to select rows 1000 through 3000 in a table
	SQL query to combine rows
	The SQL REPLACE function
	CASE expressions in the ORDER BY clause
	Finding a column value inside a user-supplied string
	Update a specific column in a field or row?
	Using BETWEEN with DATETIMEs in SQL

Oracle SQL

SQL to count values of a status code

Counting NULL columns

Detail rows for accounts that occur three times

Counting a row's NULL columns

Oracle's free SQL Developer adds database migration tool

Latest transaction if no recent prior transactions

Three ways SQL can count rows by type

SQL to select only certain times within a date range

Oracle SQL to test for numerics

Number of rows in multiple tables

Oracle SQL Research

RELATED GLOSSARY TERMS

Terms from Whatis.com − the technology online dictionary

autonomous transaction (SearchOracle.com)

CFML (SearchOracle.com)

dynamic SQL (SearchOracle.com)

foreign key (SearchOracle.com)

Java Database Connectivity (SearchOracle.com)

Open Database Connectivity (SearchOracle.com)

Oracle (SearchOracle.com)

stored procedure (SearchOracle.com)

The Open Group (SearchOracle.com)

RELATED RESOURCES

2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems

Search Bitpipe.com for the latest white papers and business webcasts

Whatis.com, the online computer dictionary

Search and Browse the Expert Answer Center
Search and browse more than 25,000 question and answer pairs from more than 250 TechTarget industry experts.

Browse our Expert Advice
Operating Systems IT Management Networking Database	Security Servers and Storage Applications and Development Career Center

SEARCH

TechTarget provides enterprise IT professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective IT purchase decisions and managing their organizations' IT projects - with its network of technology-specific Web sites, events and magazines.


TechTarget Corporate Web Site \| Media Kits \| Reprints \| Site Map All Rights Reserved, Copyright 2003 - 2008, TechTarget \| Read our Privacy Policy