E.g., IP = '' I want to extract '10.11.' or everything except what comes after the 2nd '.'

  • what the field type ? – Blag May 10 '17 at 22:17
  • I think it is a string. Not very good with SQL, sorry. – JJBee May 10 '17 at 22:18

Regex solution : SELECT REGEXP_SUBSTR("youField", '[0-9]+\.[0-9]+\.') AS "output"

check at http://rextester.com/DJN6158


I know you have an accepted answer but just for completeness here's an option without REGEX;

SELECT SUBSTR(your_field,1,INSTR(your_field,'.',1,2)-1)
FROM dual

Here is another regexp_substr using the perl \d digit operator...

    with ip_addresses(ip) as
      select '' from dual union all
      select '' from dual union all
      select '2.345.98.23' from dual
    select ip,
           regexp_substr(ip, '^\d+\.\d+\.') as truncated_ip
    from ip_addresses;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.