CODEWITHSUNDEEP
sqloracle-databasesubstrtoad
jeppoo1
jeppoo1

Reputation: 698

Oracle SQL - Select two substrings from one string and combine them in another column

Using Oracle Toad SQL.

I have one column in my test_table that has data in this kind of format:

column_1
abc-def-ghijklmn

I need to select exactly abcdef (without the '-' between them) from the row shown above. I tried it with two substrings:

SELECT (SUBSTR(column_1, 0, 3), SUBSTR(column_1, 5,3)) FROM test_table

but it does not work (the error was "missing right parenthesis"). How do I select two separate substrings from a row and combine them in a new column?

Upvotes: 0

Views: 1389

Answers (2)

jeppoo1
jeppoo1

Reputation: 698

The trick was to concatenate the two selected substrings:

SELECT CONCAT(SUBSTR(column_1, 0, 3), SUBSTR(column_1, 5,3)) FROM test_table

And in case you would need to concatenate more than two selected substrings, you have to use the concatenation operator, ||. Example:

SELECT (SUBSTR(column_1, 0, 3) || SUBSTR(column_1, 5,3) || SUBSTR(column_1, 5,2)) FROM test_table

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142798

Or (lines 1 and 2 are just sample data; you'd use line 3 for what you do. It fetches the 1st and 2nd word from the input string and concatenates them):

SQL> with test (col) as
  2    (select 'abc-def-ghijklmn' from dual)
  3  select regexp_substr(col, '\w+', 1, 1) || regexp_substr(col, '\w+', 1, 2) result
  4  from test;

RESULT
------
abcdef

SQL>

Upvotes: 1

Related Questions