CODEWITHSUNDEEP
sqloracle-database
Logan Wlv
Logan Wlv

Reputation: 3744

Insert some spaces into a string oracle database

I have a oracle table called MYTABLE , with a prog_model column. I am trying the CASE (if oracle) to insert some spaces into my prog_model depending of the string length.

My issue is that I'm getting ORA-00936 error missing expression ligne 3 c18 I have no idea where it comes from being a super noob in SQL, any ideas?

Here is my .sql : 

SELECT prog_model,
  CASE Length(prog_model)
    WHEN 11 THEN Insert('   ',2,3,prog_model)
    WHEN 12 THEN Insert('  ',2,3,prog_model)
    WHEN 13 THEN Insert(' ',2,3,prog_model)
  END
FROM MYTABLE;

EDIT:

Basically in prog_model I always have a string from 11 to 14 characters :

if 11 chars , "ABCEFGHIJKL" --> "ABC_ _ _EFGHIKL"

if 12 chars , "ABCEFGHIJKLM" --> "ABC_ _EFGHIJKLM"

Upvotes: 0

Views: 7257

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

I am not aware of an insert() function in Oracle. There is one in MySQL, though. Your error is probably because insert is a keyword.

If you wanted the spaces at the beginning, you would use the lpad() function only:

select lpad(prog_model, 14, ' ')

You seem to want them in the middle. I think this goes like:

select substr(prog_model, 1, 3) || lpad(substr(prog_model, 4), 11, ' ')

I might have the length wrong in the second part. Without sample data, it is challenging to figure out the exact length.

Upvotes: 3

William Robertson
William Robertson

Reputation: 16001

How about this (three approaches to try, including Gordon's substr/lpad):

with demo (prog_model) as
     ( select 'ABCEFGHIJKL' from dual union all
       select 'ABCEFGHIJKLM' from dual union all
       select 'ABCEFGHIJKLMN' from dual union all
       select 'ABCEFGHIJKLMNO' from dual )
select prog_model
     , regexp_replace(prog_model,'(...)(.*$)', '\1'|| rpad(' ',14 - length(prog_model)) ||'\2') as result1
     , case length(prog_model)
           when 11 then regexp_replace(prog_model,'(...)(.*$)', '\1   \2')
           when 12 then regexp_replace(prog_model,'(...)(.*$)', '\1  \2')
           when 13 then regexp_replace(prog_model,'(...)(.*$)', '\1 \2')
           when 14 then prog_model
       end as result2
     , substr(prog_model,1,3) || lpad(substr(prog_model,4),11) as result3
from demo;

PROG_MODEL     RESULT1        RESULT2        RESULT3
-------------- -------------- -------------- --------------
ABCEFGHIJKL    ABC   EFGHIJKL ABC   EFGHIJKL ABC   EFGHIJKL
ABCEFGHIJKLM   ABC  EFGHIJKLM ABC  EFGHIJKLM ABC  EFGHIJKLM
ABCEFGHIJKLMN  ABC EFGHIJKLMN ABC EFGHIJKLMN ABC EFGHIJKLMN
ABCEFGHIJKLMNO ABCEFGHIJKLMNO ABCEFGHIJKLMNO ABCEFGHIJKLMNO

Upvotes: 0

Related Questions