4 Leave Cover 4 Leave Cover - 2 months ago 9
SQL Question

SQL Query to show string before a dash

I would like to execute a query that will only show all the string before dash in the particular field.

For example:

Original data:

AB-123


After query:
AB

Answer

You can use substr:

SQL> WITH DATA AS (SELECT 'AB-123' txt FROM dual)
  2  SELECT substr(txt, 1, instr(txt, '-') - 1)
  3    FROM DATA;

SUBSTR(TXT,1,INSTR(TXT,'-')-1)
------------------------------
AB

or regexp_substr (10g+):

SQL> WITH DATA AS (SELECT 'AB-123' txt FROM dual)
  2  SELECT regexp_substr(txt, '^[^-]*')
  3    FROM DATA;

REGEXP_SUBSTR(TXT,'^[^-]*')
---------------------------
AB
Comments