Wednesday, May 22, 2024

Extract number out of character column and use it to update another column

A friend told me about the old database he had to work with. There was one file with a field that contained two pieces of information. Any leading characters, A – Z, meant one thing and the numbers within it meant something else. Unfortunately there could be any number of leading characters, the numbers could be three of four long, and there could be characters at the end. The examples he shared with me were:

  • 'A123B'
  • 'ABC123'
  • 'AB4679'
  • 'BC0123D'

His plan was to add a new numeric field to the file, and update it with the numbers extracted from this original field.

"What would be a simple way to do this with SQL?" he asked.

Before I answer that question I need a file and data within it. The DDS code for my test file is:

01 A          R TESTFILER
02 A            ORIGINAL     10A
03 A            NBR_CODE      5P 0

The file contains two fields:

Line 4: The original alphanumeric field.

Line 5: The equivalent of his new numeric field.

I can use SQL to insert the example data he gave me into the file:

02    VALUES('A123B'),('ABC123'),('AB4679'),('BC0123D') ;

This statement just added values into the first field, ORIGINAL. I can check using the following SQL statement:


The results for the above statement is:

--------  --------
A123B            0
ABC123           0
AB4679           0
BC0123D          0

It is obvious that I will need to substring the "number" from the character strings. The Substring SQL scalar function requires three pieces of information:

02         < start of string >,
03         < length of string >)

Line 1: The first piece of information is the field name we are substring the information out of, which is ORIGINAL.

Line 2: Start position of the "number" I want to substring out of the field. This could be in different places, therefore, I can use a hardcoded value.

Line 3: As the length of the "number" can vary too I cannot hardcode it too.

The best way I know to look for a range of characters in one statement is to use a Regular Expression, specifically REGEXP_INSTR. It will return to me the first position in the string the desired character(s) are found.

It has a number of parameters. The ones I will be using are:

  1. source-string:  The variable, or string, that will be searched. In this example this will be 'ORIGINAL' as that is the field I am looking within.
  2. search-pattern:  The pattern I am looking for in the source string.
  3. starting-position:  Where to start searching for the pattern.
  4. occurrence:  The occurrence I want to check string for the search pattern. In this scenario it will always be the first occurrence.

The first thing I need to find is the position first number in the field. For that I would use:

02         REGEXP_INSTR(ORIGINAL,'[0-9]',1,1)

Line 1: I want to display the field I am searching for the pattern in.

Line 2: What this REGEXP_INSTR says is:

  1. ORIGINAL:  I am looking within this field.
  2. '[0-9]':  This does need to be enclosed within apostrophes/single quotes. I am enclosing within the square brackets ( [ ] ) I am looking to any character that is in the range of zero to nine.
  3. 1:  Start searching in the first position.
  4. 1:  Stop when you find the first occurrence of a character in the desired range.

The results are:

--------  ------
A123B          2
ABC123         4
AB4679         3
BC0123D        3

Which has returned the first "numeric" character in the field.

The next thing I need to find is harder. Where is the next alphabetic character?

02         REGEXP_INSTR(ORIGINAL,'[A-Z ]',
03            (REGEXP_INSTR(ORIGINAL,'[0-9]',1,1)),
04           1)

Lines 2 - 4: This looks a lot more complicated as I have a REGEXP_INSTR nested within another REGEXP_INSTR. Let me break this line down.

Line 2: This is the start of the REGEXP_INSTR expression. I am looking for any character that is in the range of A – Z or blank.

Line 3: Start position is anywhere after the where the first "numeric" character is found.

Line 4: I am looking for the first occurrence.

The results are:

--------  ------
A123B          5
ABC123         7
AB4679         7
BC0123D        7

In the first result the letter 'B' is in the fifth position.

In the second and third results there are no letters following the "number", but there is a blank which is in the seventh position.

In the fourth result the letter 'D' is in the seventh position.

I think I have enough to be able to construct the REGEXP_INSTR expression to substring the "number" from the field.

03                REGEXP_INSTR(ORIGINAL,'[0-9]',1,1),
04                REGEXP_INSTR(ORIGINAL,'[A-Z ]',
05                     REGEXP_INSTR(ORIGINAL,'[0-9]',1,1),1) 
06                  - REGEXP_INSTR(ORIGINAL,'[0-9]',1,1))

Line 1: I want to display the content of the ORIGINAL field.

Lines 2 – 6: This is the part that substrings the "number" from ORIGINAL.

Line 2: I am substring-ing from ORIGINAL.

Line 3: The starting place is returned from this REGEXP_INSTR returns the position of the first "numeric" character.

Lines 4 – 6: This is where it gets complicated… my statement calculate the length of the "number". Which it can do:

< length of "number" > = 
   < position of alphabetic or blank character after "number" >
       - < start position of "number" >

Lines 4 and 5: This part determines the position of the first alphabetic or blank character after the "number". The REGEXP_INSTR scans for first A – Z or blank character that is found after the first part of the "number".

Line 6: From that value the starting position of the number is subtract. This is the length of the number.

When I execute this statement my results are:

ORIGINAL  00002 
--------  ------
A123B     123
ABC123    123
AB4679    4679
BC0123D   0123

The second column are character values, not numbers.

I now need to update the file NBR_CODE field in the file TESTFILE. I can take the above Select statement and modify it into an Update statement:

02    SET NBR_CODE = 
04                REGEXP_INSTR(ORIGINAL,'[0-9]',1,1),
05                REGEXP_INSTR(ORIGINAL,'[A-Z ]',
06                     REGEXP_INSTR(ORIGINAL,'[0-9]',1,1),1) 
07                  - REGEXP_INSTR(ORIGINAL,'[0-9]',1,1))

Lines 3 – 7: This substring is the same as the one in the Select statement in line 2 – 6.

Once the above is executed I can check the contents of TESTFILE with the following:


The results are:

--------  ---------
A123B           123
ABC123          123
AB4679         4679
BC0123D         123

The character "numbers" extracted by the substring statement are converted to numbers when the NBR_CODE is updated.

As you can see that this is a somewhat simple statement extracted the number from the character field and was used to update the new field in the file. All of which is what my friend had asked for.


This article was written for IBM i 7.5 TR3 and 7.4 TR9.


  1. Christian JorgensenMay 22, 2024 at 6:49 AM

    Hi Simon.

    I think a better solution would be to use the REGEXP_SUBSTR to extract the number from the text, e.g.

    02 SET NBR_CODE =
    03 REGEXP_SUBSTR(ORIGINAL, '[A-Z ]*([0-9]*).*', 1, 1, 'i', 1 )

    The "([0-9]*)" captures any consecutive digits after any number of letters or spaces, and the final '1' returns the captured group.

    For more information, see here:

    Thank you for all your great posts!

    Best regards,

  2. I love the examples of regexp* SQL tools in use as they can simply complicated code. Was there a reason REGEXP_SUBSTR was not selected as the preferred tool for extracting the data? Or is that a part two post?
    My biggest issue with REGEXP_SUBSTR is the occurrance parameter, in cases like this, you have to give it a value that is not one (1) to get the value out of the string.
    with t1 (ORIG_VALUE) as (values ('A123B'),('ABC123'),('AB4679'),('BC0123D'))
    select ORIG_VALUE
    from T1

  3. Hi Simon,
    this should be easier with REGEXP_SUBSTR because you don't need to find begin and end of the number in the string.

    Try: REGEXP_SUBSTR(ORIGINAL, '([0-9]+)', 1, 1, '', 1)

    I can't try it out right now, but if my Regex-Fu isn't completely gone, it should work.

    The () do form a group expression of at least 1 or more digits. And the BIF is extracting the first sequence of consecutive digits.

  4. VALUES REGEXP_EXTRACT('ABC123','[0-9]+')

  5. Can you squeeze out the non-digits and cast the result? I added a leading zero here in case no digits in the string.

    values(decimal('0' || regexp_replace('ABC123DEF', '[^\d]', ''), 5, 0));


  6. with mydata(fld) as (

    select fld, dec(trim(translate(upper(fld), ' ', 'ABCDEFGHJIKLMNOPQRSTUVWXYZ')),10,0)
    from mydata

    FLD | 00002
    A123B 123
    ABC123 123
    AB4679 4679
    BC0123D 123

    Another idea; strip out the letters. The regex process used here seems a bit over engineered to me.


To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.