Q
Problem solve Get help with specific problems with your technologies, process and projects.

# Extracting a part number from a text field

Our database contains a text field that users input information in that is not in a specific format. We need to...

extract data (specifically a part number) from this field for reporting purposes. What would be the best method to extract this information? The part numbers always begin with a 0, 1, 5 or 7 and are 7 characters in length.

If the text field is exactly 7 characters long, then it either contains a part number or it doesn't --

```     select textfield
from yourtable
where substring(textfield from 1 for 1)
in ('0','1','5','7')```

If it's longer than 7 characters, you're in trouble. You want my honest answer? Write a program. In robust databases like Oracle and SQL/Server, the program can be a stored procedure. But do yourself a favour and don't try it in SQL.

To get an idea how tough this is, consider that the SQL has to extract a 7-character substring from somewhere within the text field, starting at the first position within the text field where a certain character is found. The problem is, you don't have too many tools -- expressions and functions -- at your disposal for this kind of processing.

Let's start by extracting part numbers that begin with a 5 --

```     select substring( textfield
from position('5' in textfield)
for 7 )
from yourtable
where position('5' in textfield) > 0```

The WHERE condition ensures that you actually do find a '5' somewhere in the text field. That's important, because the POSITION function returns a 0 if it doesn't find what it's looking for, and then the SUBSTRING function would end up taking the first six characters of the text field (since position 0 is outside of the string).

However, the above query will still produce incorrect results. Consider the following rows --

```    row   textfield
1    part number 5093705
2    5234567 is the part number
3    i wanted 5 widgets, fred, not 3
4    6365937 is the part number```

The query above will return

```      5093705
5234567
5 widge
5937 is```

So perhaps you need to look for ' 5' instead, i.e. with a leading blank. But that wouldn't find the 2nd row above, so you'd have to test for that condition separately...

```     select substring( textfield
from position(' 5' in textfield) + 1
for 7 )
from yourtable
where position(' 5' in textfield) > 0
union all
select substring( textfield
from 1
for 7 )
from yourtable
where substring( textfield
from 1
for 1 ) = '5'```

This would still pick up rows 3 and 4 (incorrectly). And that's only the beginning of our problems, because we've only done part numbers beginning with a 5.

What you really want is a stored procedure that can loop through the text field character by character. PL/SQL and Transact-SQL can do this -- contact your resident database programmer.

• The Best SQL Web Links: tips, tutorials, scripts, and more.
• Have a SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

This was last published in November 2001

## Content

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

#### Have a question for an expert?

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.

## SearchDataManagement

• ### Ingesting data into a data lake may give you data indigestion

Big data vendors routinely push the notion of ingesting all of your data into a data lake. But in many cases, doing so is an ...

• ### Deft preprocessing marks deep learning techniques for data preparation

Deep learning techniques for data preparation include exploration of data sets and algorithms. This calls for more than a bit of ...

• ### DMBOK2 eyes evolving data management best practices, challenges

In a Q&A, DAMA president Sue Geuens talks about the second edition of the group's DMBOK reference book and how data management ...

• ### Examining the KNIME Analytics Platform for big data analytics

KNIME offers open source data analytics, reporting and integration tools, as well as commercial software that can help build more...

• ### Machine learning platforms comparison: Amazon, Azure, Google, IBM

The platform war over machine learning tools is heating up. Use our features comparison chart to see how four top vendors stack ...

• ### Exploring Oracle Advanced Analytics

Oracle Advanced Analytics and other data analytics tools, including Oracle R Enterprise and Oracle Data Miner, enable business ...

## SearchSAP

• ### SAP BW/4HANA application could streamline finance and planning

SAP Business Planning and Consolidation for BW/4HANA aims to simplify and consolidate enterprise financial planning; Business ...

• ### Choosing SAP HR module complicated by S/4HANA, SuccessFactors options

A SuccessFactors consultant explains the benefits and challenges of adding to or replacing on-premises SAP HCM with the newer ERP...

• ### Move to SAP S/4HANA Cloud improves sales app's security and performance

Emplay, an SAP Startup Focus partner, moved its Sales GPS app to SAP S/4HANA Cloud and saw improvements in performance, ...

## SearchSQLServer

• ### A closer look at Python-SQL Server 2017 integration

Do you know everything you need to take advantage of SQL Server 2017's support of Python?

• ### How much do you really know about SQL Server 2016 licensing?

Should you use Server + CAL licensing for SQL Server 2016 or does Per Core licensing offer more bang for your buck? Read our ...

## TheServerSide

• ### How Pokemon Go needed a Kubernetes powered Java cloud

How did Pokemon Go scale so well? A big part of it's scalability was the Java cloud platform that leveraged containers and ...

• ### How microservices patterns made Uber's architecture perform better

How did Uber manage to deal with the inevitable Halloween rush? Applying microservices patterns to their architecture played a ...

• ### Effective DevOps hinges on automating a continuous delivery pipeline

Many organizations struggle to bring development and operations together, only to realize that effective DevOps means ...

## SearchDataCenter

• ### Distributed data centers boost resiliency, but IT hurdles remain

Distributed data center architectures increase IT resiliency compared to traditional single-site models, with networking, data ...

• ### Server firmware security threat, real or not, gets renewed attention

The latest threat to server security could be the firmware. That's the emphasis of at least one server-maker, hoping to help ...

• ### Assess and compare DCIM software options in the market

DCIM software can improve the management and operation of your data center. Choose the right vendor for your company to control ...

## SearchContentManagement

• ### M-Files puts content first by adding AI to content management tools

M-Files is adding AI functionality to its platform to better assist in document search, focusing on what a document is, rather ...

• ### Combining DAM and DX for digital experience management

Digital asset management systems predate the web. Cutting-edge digital experiences, however, depend on keeping DAM in order, with...

• ### Three BI dashboard best practices you need to know

As companies struggle with BI integration, Microsoft has figured out the magic formula with its Power BI dashboard. Here are the ...

## SearchFinancialApplications

• ### Bundled payments healthcare tool finds better deals for employers

Trying to rein in healthcare costs, large self-insured employers are using HR tech tools, such as analytics-based bundled payment...

• ### Ceridian uses employee engagement tool from its own joint venture

After forming a new joint venture, LifeWorks, HCM vendor Ceridian uses LifeWorks' employee recognition, engagement and perks ...

• ### Evaluate HR technology to fit your talent management processes

Choose your talent management system wisely or risk irking your employees. Learn the criteria for evaluating software that ...

Close