Results 1 to 4 of 4
  1. #1
    Join Date
    May 2013
    Posts
    2

    Unanswered: Regular Expression: Find number preceding a string

    Hello,

    I have a string column. I want to extract the 1-2 digit number preceding a specific format: 4X100. So basically, the desired value can be anywhere in the string:

    Some text here 4X100 and some text here
    16X160 Some text here
    Some text here 2X90

    How do I just extract just the number preceding the X? So in the first example, all I want is a 4.

    Would something like this work?

    SELECT Replace(REGEXP_SUBSTR('Some text here 4X100 and some text here', '[0-9]+X'), 'X', '') RESULT FROM DUAL;

    Thanks!
    Last edited by r@v3n; 05-14-13 at 00:26.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If you tried it, you'd see (yes, it returns the result you want).

    Though, there's a simpler regular expression that does the same:
    Code:
    SQL> with test as
      2    (select 'Some text here 4X100 and some text here' col from dual union
      3     select '16X160 Some text here' from dual union
      4     select 'Some text here 2X90' from dual
      5    )
      6  select
      7    col,
      8    replace(regexp_substr(col, '[0-9]+X'), 'X', '') your_result,
      9    regexp_substr(col, '\d+') my_result
     10  from test;
    
    COL                                     YOUR_RESULT MY_RESULT
    --------------------------------------- ----------- -----------
    16X160 Some text here                   16          16
    Some text here 2X90                     2           2
    Some text here 4X100 and some text here 4           4
    
    SQL>
    Certainly, it wouldn't work if there's any other number in front of these in your example. But, as you didn't specify that case, it's OK.

  3. #3
    Join Date
    May 2013
    Posts
    2
    Thank you, for your helpful post. I failed to mention that there could be another numeric value either before or after the specific text that I'm looking for. My intent is not to find the first number sequence that comes up, but rather, the digits preceding the "X" and then some numbers e.g., 16X500, or 4X25.

    Any additional thoughts would be greatly appreciated!

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Right; in that case, your attempt does the job better than mine.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •