CODEWITHSUNDEEP
sqlregexoracle-databaseregexp-substr
nopnop
nopnop

Reputation: 13

how to use regexp_substr

i have call number :

TX353 G35 1992
Ref QP141 B151 R4 1956
RM216 M285 K5 1996
T385 C22 1960
Ths LB500 M200 A5 1998

i want to result:

TX353 G35 1992          =>TX
Ref QP141 B151 R4 1956  =>QP
RM216 M285 K5 1996      =>RM
T385 C22 1960           =>T
Ths LB500 M200 A5 1998  =>LB

i use:

SELECT REGEXP_SUBSTR(callnumber,'[A-Z]+')

result not correct TX R RM T T

Upvotes: 0

Views: 6110

Answers (4)

Boneist
Boneist

Reputation: 23578

Here's one way of doing it:

WITH sample_data AS (select 'TX353 G35 1992' str from dual union all
                     select 'Ref QP141 B151 R4 1956' str from dual union all
                     select 'RM216 M285 K5 1996' str from dual union all
                     select 'T385 C22 1960' str from dual union all
                     select 'Ths LB500 M200 A5 1998' str from dual union all
                     select 'X12345' str from dual union all
                     select 'Y F123' str from dual)
SELECT str,
       regexp_substr(str, '([A-Z]{1,2})[[:digit:]]*( |$)', 1, 1, NULL, 1) sub_str
FROM   sample_data;

STR                    SUB_STR
---------------------- ----------------------
TX353 G35 1992         TX
Ref QP141 B151 R4 1956 QP
RM216 M285 K5 1996     RM
T385 C22 1960          T
Ths LB500 M200 A5 1998 LB
X12345                 X
Y F123                 Y

This looks for the pattern of one or two upper case letters followed by 0 or more digits followed by a space or the end of the line. (If you want to restrict the number of digits to 1 or more, change the * to a +.)

We put brackets around the "one or two upper case letters" to label it as a subexpression, which we can then request to be output in the regexp_substr (that's the final parameter, which in our case is 1 because our desired subexpression is the first one encountered).

If you have other special characters that could follow your desired pattern, then you can simply expand the OR section (currently ( |$)), e.g. if you wanted to include a question mark, the OR section would become ( |?|$)

Upvotes: 0

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

Try this. To look for two consecutive Caps. I used NVL() to attempt for taking the single occurrence of Capital letter.

Credits to MT0 for that

[A-Z]{2}

Example:

with my_data(str) as
(
  select 'TX353 G35 1992' from dual
  union all
  select 'Ref QP141 B151 R4 1956' from dual
  union all
  select 'RM216 M285 K5 1996' from dual
  union all
  select 'T385 C22 1960' from dual
  union all
  select 'Ths LB500 M200 A5 1998' from dual

)
  select str,NVL(regexp_substr(str,'[A-Z]{2,}'),regexp_substr(str,'([A-Z]+)\d',1,1,NULL,1)) from my_data;

Output :

TX353 G35 1992          TX
Ref QP141 B151 R4 1956  QP
RM216 M285 K5 1996      RM
T385 C22 1960           T
Ths LB500 M200 A5 1998  LB

EDIT:

If you need to extract the the full sequence of CAPS.

You need this, [A-Z]{2,}

Upvotes: 0

MT0
MT0

Reputation: 167972

You appear to want the first upper-case letters that are followed by some digits:

Oracle Setup:

CREATE TABLE your_table ( your_column ) AS
  SELECT 'TX353 G35 1992'         FROM DUAL UNION ALL
  SELECT 'Ref QP141 B151 R4 1956' FROM DUAL UNION ALL
  SELECT 'RM216 M285 K5 1996'     FROM DUAL UNION ALL
  SELECT 'T385 C22 1960'          FROM DUAL UNION ALL
  SELECT 'Ths LB500 M200 A5 1998' FROM DUAL UNION ALL
  SELECT 'Ref A123 B456 C7 2000'  FROM DUAL;

Query:

SELECT REGEXP_SUBSTR(
         your_column,
         '([A-Z]+)\d',
         1,     -- Start at the first character
         1,     -- Get the first match
         NULL,  -- Case sensitive
         1      -- Return the first capture group
       ) As match
FROM   your_table

Output:

MATCH
-----
TX
QP
RM
T
LB
A

Upvotes: 2

M Zippka
M Zippka

Reputation: 103

remove "Ref" and "Ths" from the call number and your code works

Upvotes: 0

Related Questions