CODEWITHSUNDEEP
sqloracle-databasesubstr
4 Leave Cover
4 Leave Cover

Reputation: 1276

Retrieve segment from value

I have this value in my field which have 5 segment for example 100-200-300-400-500.

How do I query to only retrieve the first 3 segment? Which mean the query result will display as 100-200-300.

Upvotes: 0

Views: 151

Answers (3)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

((\d)+-(\d)+-(\d)+)

If the Position of this sequence is arbitrary, you might go for REGularEXPressions

select regexp_substr(
                      'Test-Me 100-200-300-400-500 AGain-Home',
                      '((\d)+-(\d)+-(\d)+)'
                    ) As Result
from dual

RESULT
-----------
100-200-300

Otherwise Simple SUBSTR will do

Upvotes: 1

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

The old SUBSTR and INSTR will be faster and less CPU intensive as compared to REGEXP.

SQL> WITH DATA AS(
  2  SELECT '100-200-300-400-500' str FROM dual
  3  )
  4  SELECT substr(str, 1, instr(str, '-', 1, 3)-1) str
  5  FROM DATA
  6  /

STR
-----------
100-200-300

SQL>

The above SUBSTR and INSTR query uses the logic to find the 3rd occurrence of the hyphen "-" and then take the substring from position 1 till the third occurrence of '-'.

Upvotes: 2

Denis Kohl
Denis Kohl

Reputation: 750

you have tow way, the first is substring.

The second is fast, us a REGEXP like this.

 REGEXP_SUBSTR('100-200-300-400-500','[[:digit:]]{3}-[[:digit:]]{3}-[[:digit:]]{3}')"REGEXPR_SUBSTR" FROM DUAL;

Upvotes: -1

Related Questions