Convert String to char in oracle

Question

Hi i have the following data '1,2,3' and i need to convert this into this '1','2','3' that i will use in this

Select * from dual where to_chat(month) in ('1','2','3')

i cannot use query straight like that because of the quater

Quater no.    Month
1             1,2,3
2             4,5,6
...

so i must use case like this but case statement return on format like this '1','2','3'

 Select * from dual 
where to_chat(month) in (
select 
case quarter 
when 1 then '1','2','3' 
when 2 then '4','5','6' 
end 
from dual )

input

'1,2,3'

expected output

'1','2','3'

Regardless, you can't do that. every value in IN clause is a separate item. Look at this - https://stackoverflow.com/questions/4672545/using-the-in-clause-with-a-comma-delimited-string-from-the-output-of-a-replace — OldProgrammer, Jan 05 '21 at 01:08
In any case, MONTH and QUARTER should be numbers, so why are you handling them as strings? If they are both numbers (as they should be), and you want to select "where month matches quarter" (as you seem to want), that is trivial: `... where QUARTER = ceil(MONTH/3)` — , Jan 05 '21 at 01:42
hi @marhguy yes it numbers but on the table there's no quater column so where QUARTER = is not gonna work — Kim Ivan Bay-an, Jan 05 '21 at 02:06
@KimIvanBay-an If there is no quarter column in the table then why do you have it in your sample code with the case statement? If you can add some sample data and desired result, that will clear up the confusion for everyone here — Radagast, Jan 05 '21 at 03:41

score 0 · Answer 1 · answered Jan 05 '21 at 01:08

0

You can use regexp_like():

where regexp_like(<whatever>, replace('1,2,3', ',', '|'))

Putting single quotes in the string still won't enable you to do what you want with like. It will still be a single string, but one that has single quotes and commas in it.

answered Jan 05 '21 at 01:08

Gordon Linoff

1,242,037
58
646
786

would you provide full query example , sorry i canoot understand what you mean im kida new at this – Kim Ivan Bay-an Jan 05 '21 at 01:15
because if i use that replace('1,2,3', ',', '|') would get the 1 if i input 10 replace('10,11,12', ',', '|') – Kim Ivan Bay-an Jan 05 '21 at 01:21

score 0 · Answer 2 · answered Jan 05 '21 at 08:46

How about such an approach? Example is based on Scott's EMP table. I'd like to fetch employees which were hired in certain months.

Sample data:

SQL> select deptno, ename, job, hiredate, to_char(hiredate, 'mm') mon from emp order by mon;

    DEPTNO ENAME      JOB       HIREDATE   MO
---------- ---------- --------- ---------- --
        20 ADAMS      CLERK     12.01.1983 01
        10 MILLER     CLERK     23.01.1982 01
        30 ALLEN      SALESMAN  20.02.1981 02  -- suppose I want to select employees
        30 WARD       SALESMAN  22.02.1981 02  -- hired in February,
        20 JONES      MANAGER   02.04.1981 04  -- April and
        30 BLAKE      MANAGER   01.05.1981 05  -- May
        10 CLARK      MANAGER   09.06.1981 06
        30 TURNER     SALESMAN  08.09.1981 09
        30 MARTIN     SALESMAN  28.09.1981 09
        10 KING       PRESIDENT 17.11.1981 11
        20 SCOTT      ANALYST   09.12.1982 12
        20 SMITH      CLERK     17.12.1980 12
        30 JAMES      CLERK     03.12.1981 12
        20 FORD       ANALYST   03.12.1981 12

14 rows selected.

Query - which splits input search string into rows (so that you could use it in IN clause) would then be:

SQL> with src as
  2    (select &par_month src_month from dual)
  3  select deptno, ename, job, hiredate
  4  from emp
  5  where to_number(to_char(hiredate, 'mm')) in
  6    (select regexp_substr(src_month, '[^,]+', 1, level)
  7     from src
  8     connect by level <= regexp_count(src_month, ',') + 1
  9    )
 10  order by to_char(hiredate, 'mm');
Enter value for par_month: '2,4,5'

    DEPTNO ENAME      JOB       HIREDATE
---------- ---------- --------- ----------
        30 ALLEN      SALESMAN  20.02.1981
        30 WARD       SALESMAN  22.02.1981
        20 JONES      MANAGER   02.04.1981
        30 BLAKE      MANAGER   01.05.1981

SQL>

Convert String to char in oracle

2 Answers2