Reading a part of a alpha numeric string in SQL

Question

I have a table with one column " otname " table1.otname contains multiple rows of alpha-numeric string resembling the following data sample:

11.10.32.12.U.A.F.3.2.21.249.1

2001.1.1003.8281.A.LE.P.P

2010.1.1003.8261.A.LE.B.B

I want to read the fourth number in every string ( part of the string in bold ) and write a query in Oracle 10g to read its description stored in another table. My dilemma is writing the first part of the query.i.e. choosing the fourth number of every string in a table

My second query will be something like this:

select description_text from table2 where sncode = 8281  -- fourth part of the data sample in every string

Many thanks.

novice

Answer 1

Works with 9i+:

WITH portion AS (
  SELECT SUBSTR(t.otname, INSTR(t.otname, ".", 1, 3)+1, INSTR(t.otname, ".", 1, 4)) 'sncode'
    FROM TABLE t)
SELECT t.description_text
  FROM TABLE2 t
  JOIN portion p ON p.sncode = t.sncode

The use of SUBSTR should be obvious; INSTR is being used to find location the period (.), starting at the first character in the string (parameter value 1), on the 3rd and 4th appearance in the string. You might have to subtract one from the position returned for the 4th instance of the period - test this first to be sure you're getting the right values:

SELECT SUBSTR(t.otname, INSTR(t.otname, ".", 1, 3)+1, INSTR(t.otname, ".", 1, 4)) 'sncode'
 FROM TABLE t

I used subquery factoring so the substring happens before you join to the second table. It can be done as a subquery, but subquery factoring is faster.

Answer 2

Newer versions of oracle (including 10g) have various regular expression functions. So you can do something like this:

where sncode = to_number(regexp_replace(otname, '^(\d+\.\d+\.\d+\.(\d+))?.+$', '\2'))

This matches 3 sets of digits-followed-by-a-dot, and a fourth grouped set of digits, followed by the rest of the string, and returns a string consisting of all that entirely replaced by the first group (the fourth set of digits).

Here's a complete query (if I understood your description of the two tables correctly):

select t2.description_text
from table1 t1, table2 t2
where t2.sncode = to_number(regexp_replace(t1.otname, '^(\d+\.\d+\.\d+\.(\d+))?.+$', '\2'))

Another slightly shorter alternative regex:

where t2.sncode = to_number(regexp_replace(t1.otname, '^((\d+\.){3}(\d+))?.+$', '\3'))