CODEWITHSUNDEEP
sqloracle-databasesubstring
Aruna Raghunam
Aruna Raghunam

Reputation: 921

How to get string after character oracle

I have VP3 - Art & Design and HS5 - Health & Social Care, I need to get string after '-' in Oracle. Can this be achieved using substring?

Upvotes: 18

Views: 137143

Answers (3)

MT0
MT0

Reputation: 167982

You can use:

SELECT CASE
       WHEN INSTR(value, '-') > 0
       THEN SUBSTR(value, INSTR(value, '-') + 1)
       END AS subject
FROM   table_name

or

SELECT REGEXP_SUBSTR( value, '-(.*)$', 1, 1, NULL, 1 ) AS subject
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name ( value ) AS
SELECT 'VP3 - Art & Design and HS5 - Health & Social Care' FROM DUAL UNION ALL
SELECT '1-2-3' FROM DUAL UNION ALL
SELECT '123456' FROM DUAL

Both output:

| SUBJECT                                      |
| :------------------------------------------- |
|  Art & Design and HS5 - Health & Social Care |
| 2-3                                          |
| null                                         |

Trimming leading white-space:

If you want to trim the leading white-space then you can use:

SELECT CASE
       WHEN INSTR(value, '-') > 0
       THEN LTRIM(SUBSTR(value, INSTR(value, '-') + 1))
       END AS subject
FROM   table_name

or

SELECT REGEXP_SUBSTR( value, '-\s*(.*)$', 1, 1, NULL, 1 ) AS subject
FROM   table_name

Which both output:

| SUBJECT                                     |
| :------------------------------------------ |
| Art & Design and HS5 - Health & Social Care |
| 2-3                                         |
| null                                        |

Why the naive solutions don't always work:

SELECT SUBSTR(value, INSTR(value, '-') + 2) AS subject
FROM   table_name

Does not work in 2 cases:

  • It finds the index of the - character and then skips 2 characters (the - character and then the assumed white-space character); if the second character is not a white-space character then it will miss the first character of the substring (i.e. if the input is 1-2-3 then the output would be -3 rather than 2-3).
  • It assumes that there will always be a - character in the string; if this is not the case then it will erroneously return the substring starting from the second character rather than returning NULL (i.e. if the input is 123456 then the output is 23456 rather than NULL).

Using the regular expression:

SELECT REGEXP_SUBSTR(value, '[^-]+', 1, 2)
FROM   table_name

Does not find the substring after the 1st - character; it will find the substring between the 1st and 2nd - characters and strip any characters outside that range (inclusive of the - characters). So if the input is VP3 - Art & Design and HS5 - Health & Social Care then the output is Art & Design and HS5 rather than the expected Art & Design and HS5 - Health & Social Care.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269793

You can use regexp_substr():

select regexp_substr(col, '[^-]+', 1, 2)

If you want to remove an optional space, then you can use trim():

select trim(leading ' ', regexp_substr(col, '[^-]+', 1, 2))

The non-ovious parameters mean

  • 1 -- search from the first character of the source. 1 is the default, but you have to set it anyway to be able to provide the second parameter.
  • 2 -- take the second match as the result substring. the default would be 1.

Upvotes: 15

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

For a string operation as simple as this, I might just use the base INSTR() and SUBSTR() functions. In the query below, we take the substring of your column beginning at two positions after the hyphen.

SELECT
    SUBSTR(col, INSTR(col, '-') + 2) AS subject
FROM yourTable

We could also use REGEXP_SUBSTR() here (see Gordon's answer), but it would be a bit more complex and the performance might not be as good as the above query.

Upvotes: 28

Related Questions