Concatenating values from a variable in Oracle SQL

Question

I have a problem with my SQL report. I passed values from my column to a variable. Now, I need to show values from my variable ('@{EMPLOYEES}') with a ' ' at the beginning and ending of each name. To clear this out:

My variable holds the values like one long string:

('Employee1, Employee2, Employee3, Employee4')

And I need it to hold the values like separate strings:

('Employee1','Employee2','Employee3','Employee4')

Can this be somehow done by CONCAT function? If so, then how?

You already have a single quote at begining and end? You can just do a replace on the comma. Replace "," with "','" so replace comma with single quote, comma, and single quote. That will put single quotes in the middle sections of all your values. — Brad, Sep 14 '18 at 13:04
So like that ? : SELECT MyEmployeeColumn FROM MyEmployeeTable where MyEmployeeColumn in (REPLACE ('@{EMPLOYEES}', "," , "','")) ? — Alwexistwo, Sep 14 '18 at 13:07
If you're trying to use the strings in an IN condition then I think you instead want the SPLIT function https://stackoverflow.com/questions/28677070/split-function-in-oracle-to-comma-separated-values-with-automatic-sequence Try something like SELECT MyEmployeeColumn FROM MyEmployeeTable where MyEmployeeColumn in (SPLIT (@{EMPLOYEES})) — Ryan Dawson, Sep 14 '18 at 13:13
Doing the replace like that it would seperate the values by comma, but you can not do it like that in an IN statement (did not see that was your requirement). You would have to use a different function for the IN Statement — Brad, Sep 14 '18 at 13:13

score 0 · Answer 1 · answered Sep 14 '18 at 13:13

0

Try this:

select chr(39) || 'word' ||chr(39)  from dual

answered Sep 14 '18 at 13:13

starko

1,150
11
26

score 0 · Accepted Answer · answered Sep 14 '18 at 13:16

0

Since you're trying to split a comma-separated string and pass the output into an IN-condition in oracle (as you clarify in the comments), you can follow the example in https://blogs.oracle.com/aramamoo/how-to-split-comma-separated-string-and-pass-to-in-clause-of-select-statement

answered Sep 14 '18 at 13:16

Ryan Dawson

11,832
5
38
61

Do you by any chance know how to apply this in Oracle BI environment? There instead on tables you operate on Subject Areas. So the code from this site gives me the following error: "Nonexistent table: "dual". (HY000)" and even if I replace the dual with any subject area i get: "Nonexistent table: "Sales - CRM Pipeline". (HY000)" – Alwexistwo Sep 14 '18 at 13:47
Not something I know about but I guess you're hitting the same problem as https://it.toolbox.com/question/how-to-use-dual-table-in-reports-011212 i.e. you can't use 'dual' in BI. One of the solutions suggested there is to perform a direct database request (https://gerardnico.com/dat/obiee/obips/direct_database_request). There are also other suggestions there but for direct db requests I can see examples of people running queries much like this one https://gerardnico.com/dat/obiee/obis/dummy_table. – Ryan Dawson Sep 14 '18 at 15:01
The regex used in that link in your post will fail if the delimited string has NULL elements. See this post for more info: https://stackoverflow.com/a/31464699/2543416 – Gary_W Sep 14 '18 at 17:37

score 0 · Answer 3 · answered Sep 14 '18 at 16:41

Late to the game here, I enjoy playing with Common Table Expressions. Here is how you can split your string using a CTE:

WITH
    setup AS (SELECT 'a,b,c,d' letter FROM DUAL),
    initialize AS
        (SELECT letter || ',' AS letter
           FROM setup),
    splitset (letter, rest) AS
        (SELECT SUBSTR (letter, 1, INSTR (letter, ',') - 1) AS letter
              , SUBSTR (letter, INSTR (letter, ',') + 1)    AS rest
           FROM initialize
         UNION ALL
         SELECT SUBSTR (rest, 1, INSTR (rest, ',') - 1) AS letter
              , SUBSTR (rest, INSTR (rest, ',') + 1)    AS rest
           FROM splitset
          WHERE rest IS NOT NULL)
SELECT letter
  FROM splitset;

Setup is just setting up some sample data, your code would start with 'intialize'.

Concatenating values from a variable in Oracle SQL

3 Answers3