CODEWITHSUNDEEP
sqloracle-database
Felipe Santos
Felipe Santos

Reputation: 25

Get string until character Oracle SQL

How to get string before character?

I need to get string before ; in Oracle SQL.

For example:

147739 - Blablabla ; Blublublu

Needed output:

147739 - Blablabla

My code so far:

SELECT  
    UPPER(CONVERT(REGEXP_REPLACE(SUBSTR(HISTORICO, INSTR(HISTORICO, 'Doc') + 4), 'S/A', 'SA'), 'US7ASCII')) 
FROM 
    GEQ_GL_CONC_CONTABIL_FRETES_V 
WHERE 
    periodo = '$Periodo$' AND livro = 'ESMALTEC_FISCAL'

I want the whole string up to ;

Upvotes: 0

Views: 1280

Answers (3)

hmarques
hmarques

Reputation: 406

A few alternatives using REGEXP The result with each solution depends of how uniform your data is

WITH tbl
AS (
  SELECT '147739 - Blablabla ; Blublublu' str 
    FROM DUAL
)
SELECT TRIM(REGEXP_SUBSTR(str, '([[:alnum:]]|-| )*')) AS SOLUTION_1
,      REGEXP_SUBSTR(str, '[[:digit:]]*( )?(-)?( )?[[:alpha:]]*') AS SOLUTION_2
,      REGEXP_SUBSTR(str, '[[:digit:]]*( |-)*[[:alpha:]]*') AS SOLUTION_3
  FROM tbl;

Upvotes: 0

slowko
slowko

Reputation: 875

We can use a combination of SUBSTR and INSTR to achieve this;

SELECT SUBSTR(FIELD_NAME,1,INSTR(FIELD_NAME,';', 1, 1)-1) FROM TABLE_NAME;

The first argument to SUBSTR is the position in the field value from which we want to start (1 = at the beginning), the second argument is the length of the substring we want to read, here it is synonymous with the position of ';' -1.

The third and fourth arguments to INSTR are where to start searching for ';' and the count we are interested in. In our example that is from the beginning (1) and the first occurence (again 1).

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133400

You could try using substr() and instr() 

select  SUBSTR(my_col, 0, INSTR(my_col, ';')-1) 
from my_table   

select  SUBSTR(' Blablabla ; Blublublu', 0, INSTR('A Blablabla ; Blublublu', ';')-1) 
from dual

Upvotes: 1

Related Questions