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

# Sorting substrings of different lengths numerically

I have a table which contains IP addresses, such as 192.192.192.50. I want to display the table sorted by IP address...

(which is the primary key, a string), but unfortunately 192.192.192.19 will appear before 192.192.192.2!

The general solution to sorting strings of numbers of different lengths is to concatenate each of them onto the right end of a string of enough zeroes, and then take a substring of the desired length from the right.

For example, if we concatenate '123', '45', and '6' onto '00', we get '00123', '0045', and '006'. If we then take the rightmost 3 characters, we get '123', '045', and '006'. This is the strategy we shall apply to the IP numbers.

IP numbers contain four groups of digits, each of which can be between 1 and 3 digits long. We need a way to search a string or substring looking for the first occurrence of a specific character, in this case a period. We also need to pull substrings out, pad them with zeroes where necessary, and take the rightmost three characters to sort on. Each of the four groups can vary in size, so this will get tricky.

Standard SQL provides the SUBSTRING and POSITION functions, but these aren't supported by every database. For the following examples, I'll use Microsoft Access syntax, and the MID, INSTR, and RIGHT functions -- MID and RIGHT are special versions of SUBSTRING, and INSTR performs the POSITION function.

Let's get the first group of digits. We need a substring, starting at the very first character on the left, and going up to the last character before the first period --

```MID( IPno
, 1
, INSTR(IPno,'.')-1
)```

INSTR(IPno,'.') returns the position of the first period, which will be 2, 3, or 4, depending on whether the first group of digits is 1, 2, or 3 digits long. The MID function starts at the first character of IPno, and goes for a length equal to the position of the first period minus 1, i.e. up to the last character before the period.

Once we have pulled out the digits of the first group, we need to pad them on the left with zeroes and take the rightmost 3 characters --

```RIGHT( '00'
& MID( IPno
, 1
, INSTR(IPno,'.')-1
)
, 3
)```

From here on it gets messier. To pull out the second group of digits, we have to start one character to the right of the first period, search for the next period in that substring, and then pull out the second group like this --

```MID(IPno
,INSTR(IPno,'.')+1
,INSTR(MID(IPno
,INSTR(IPno,'.')+1)
,'.')-1)```

Notice that the length parameter, which determines how long a substring we're getting, is based on the position of the second period.

This response is continued.

This was last published in June 2002

## 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

• ### 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 ...

• ### MongoDB Atlas footprint grows bigger with Azure, Google cloud support

MongoDB has expanded cloud coverage for its Atlas hosted database service, with Azure and Google versions joining an initial ...

• ### 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

• ### 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 ...

Adobe rounds out its cloud suite with Adobe Scan, which allows mobile employees to capture documents and images and turn them ...

## 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