Insert some spaces into a string oracle database

Question

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"

Looks like you are trying to use the MySQL insert() function in Oracle. Oracle doesn't have this. From looking at the MySQL docs, it seems prog_model should be the first argument - however I don't know exactly what you need it to do. — William Robertson
– William Robertson, Commented Feb 15, 2018 at 11:58

Gordon Linoff · Accepted Answer · 2018-02-15 13:20:12Z

3

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.

edited Feb 15, 2018 at 13:20

answered Feb 15, 2018 at 11:27

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Logan Wlv Over a year ago

Thanks for the answer I'll try it out , I did some edits and here the insert function I'm using : oracle_functions

William Robertson Over a year ago

That's a link to Oracle Data Visualization Desktop. if you are using that and not a standard SQL tool then you should probably tag it.

Logan Wlv Over a year ago

I am using a standard SQL tool, so yeah it explains why Insert is not working there. @WilliamRobertson

William Robertson Over a year ago

Standard Oracle SQL functions are here (12.1): docs.oracle.com/database/121/SQLRF/functions.htm

William Robertson · Accepted Answer · 2018-02-15 13:19:24Z

0

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

edited Feb 15, 2018 at 13:19

answered Feb 15, 2018 at 13:02

William Robertson

16.2k4 gold badges40 silver badges49 bronze badges

2 Comments

Logan Wlv Over a year ago

looks super difficult compared to what I need to do, no?

William Robertson Over a year ago

OK, Gordon's substr/lpad is a simpler way to do it, with a bit of tweaking to match your test data. Adding...

Collectives™ on Stack Overflow

Insert some spaces into a string oracle database

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related