CODEWITHSUNDEEP
sqloracle-database
4 Leave Cover
4 Leave Cover

Reputation: 1276

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

Upvotes: 2

Views: 15514

Answers (3)

Abhijith Kolanakuduru
Abhijith Kolanakuduru

Reputation: 83

I found this simple

 SELECT distinct
        regexp_replace(d.pyid, '-.*$', '') as result
    FROM schema.table d;

pyID column contains ABC-123, DEF-3454

SQL Result:

ABC DEF

Upvotes: 1

stefan.schwetschke
stefan.schwetschke

Reputation: 8932

You can use regexp_replace.

For example

    WITH DATA AS (
         SELECT 'AB-123' as text FROM dual
         UNION ALL
         SELECT 'ABC123' as text FROM dual
    )
    SELECT 
        regexp_replace(d.text, '-.*$', '') as result
    FROM DATA d;

will lead to

    WITH DATA AS (
      2      SELECT 'AB-123' as text FROM dual
      3      UNION ALL
      4      SELECT 'ABC123' as text FROM dual
      5  )
      6  SELECT
      7     regexp_replace(d.text, '-.*$', '') as result
      8  FROM DATA d;

    RESULT
    ------------------------------------------------------
    AB
    ABC123

Upvotes: 0

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

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

Upvotes: 4

Related Questions