1

i have a column containing hostnames in the format of :

oraclehost.server.region.company.net

How to extract the oraclehost part from the hostname i.e the string before the first ..

Please sugges.Thanks.

ZINE Mahmoud
  • 1,272
  • 1
  • 17
  • 32
anand
  • 47
  • 8
  • 1
    Check answer on this [question](https://stackoverflow.com/questions/4389571/how-to-select-a-substring-in-oracle-sql-up-to-a-specific-character) – Marko Ivkovic Apr 21 '21 at 09:29

2 Answers2

5
SELECT REGEXP_SUBSTR(HOSTNAMES, '[^.]+', 1, 1) FROM MYTABLE;
Aman Singh Rajpoot
  • 1,451
  • 6
  • 26
1

Alternatively, substr + instr combination which would probably perform better for large data sets:

substr(hostnames, 1, instr(hostnames, '.') - 1)

For example:

SQL> with mytable (hostnames) as
  2    (select 'oraclehost.server.region.company.net' from dual)
  3  select substr(hostnames, 1, instr(hostnames, '.') - 1) result
  4  from mytable;

RESULT
----------
oraclehost

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • Hi, could you please explain how it will perform better. I think it's something related to the internal working of regexp_substr with cost more CPU. – Aman Singh Rajpoot Apr 21 '21 at 09:40
  • This returns `NULL` (rather than the entire string) if there is no period character in the string. [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=1689d2bb0306db8a57553ac8cb5246e7) – MT0 Apr 21 '21 at 09:50
  • The OP said: "column containing hostnames in the format of : oraclehost.server.region.company.net" so ... there should be period character in the string. 4 of them, actually, @MT0. If that's not what they have, they should have said so, I guess. – Littlefoot Apr 21 '21 at 09:53
  • In the OP's case, yes. However, there are likely to be many other users who visit questions with similar (but not identical) problems and the limitation of this answer on the generic problem from the title of the OP's question "how to get the string before first occurrence of a special character" is that it will not handle cases when the special character is not present and this is worth highlighting to future readers. – MT0 Apr 21 '21 at 09:55
  • Absolutely, no objections to what you've just said, @MT0. – Littlefoot Apr 21 '21 at 10:00
  • No answer to my question. :( – Aman Singh Rajpoot Apr 21 '21 at 11:38
  • What do you mean by saying that, Aman? – Littlefoot Apr 21 '21 at 11:46
  • I was asking "Hi, could you please explain how it may perform better"? @Littlefoot – Aman Singh Rajpoot Apr 25 '21 at 12:08
  • Aha. If you have a table that contains *millions* of rows, regular expressions will be slower than SUBSTR + INSTR. Have a look at Mathguy's answer, here: https://stackoverflow.com/questions/41156391/performance-and-readability-of-regexp-substr-vs-instr-and-substr. During his testing, it turned out that regexp was 40 times SLOWER than SUBSTR + INSTR. – Littlefoot Apr 25 '21 at 19:22