Search code examples
sqloracle-databasesubstr

Substring of a specific occurence

I have a column as varchar2 datatype, the data in it is in format:

100323.3819823.222
100.323123.443422
1001010100.233888
LOL12333.DDD33.44

I need to remove the whole part after the first occurrence of '.'

In the end it should look like this:

100323
100
1001010100
LOL12333

I cant seem to find the exact substring expression due to the fact that there is not any fix length of the first part.

Solution

  • One way is to use REGEXP_SUBSTR:

    SELECT REGEXP_SUBSTR(column_name,'^[^.]*') FROM table

    The other way is to combine SUBSTR with INSTR, which is a bit faster, but will result in NULL if the data doesn't contain a dot, so you'll have to add a switch if needed:

    SELECT SUBSTR(column_name, 1, INSTR(column_name,'.') - 1) FROM table