Adding a dot in a string in sql

I need to add a dot in two strings of numbers. (example “88188741478”). I need to add it 8 characters before the end. The result should look like this “881.88741478”. I need several columns to be returned but need to add the dot only in 2 of them.

Final output is should be something like this:

380600001036641|8.84243987|44.41515810|0

I am using sqlplus in a .bat file to run a .sql file.

My code is:

whenever sqlerror exit 90
set echo on
set verify off
set feedback off
set heading off
set linesize 257
set termout off
set pages 0
set pagesize 0
set space 0
spool Cfile.txt
Select
First
||'|'|| Second
||'|'|| Third
||'|'|| STUFF(Fourth, length(Fourth)-8, 0, '.') as "FOURTH"
||'|'|| STUFF(Fifth, length(Fifth)-8, 0, '.') as "FIFTH"
||'|'|| Sixth
FROM table
WHERE Sixth='0'
AND NotSelectedParameter IN ('001', '003');

I keep getting this error:

#ORA-00923: FROM keyword not found where expected

I tried changing aliases, checking for reserved words, using substring, substr, left + right instead of stuff but i keep getting different errors. In the times I don’t get an error, the programs get blocked printing the line’s number of last command (if “AND NotSelectedParameter IN (‘001’, ‘003’);” is at line 12 it prints 12 and it blocks itself until i ctrl z in the cmd).

What can I do?

Edit: the unclosed identifier was a formatting error in this question. The identifier was closed in the original code.

Answer

You can’t have (unterminated) aliases in the middle of a function.

Remove both “AS …” .

A function that does exist is regexp_replace.

regexp_replace('88188741478','(.*)([[:digit:]]{8})$','1.2')

gives

881.88741478

Advertisement

Answer