Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Thanks for the question, Shyam.

Asked: February 12, 2020 - 2:51 pm UTC

Last updated: February 14, 2020 - 12:35 pm UTC

Version: 4.2

Viewed 1000+ times

You Asked

Hi Chris,
I want to restrict the length of the input string to 8 characters by adjusting all the digits (after 'MFT' in below example) from Input string.
Means, want to accommodate all the digits in string.

Ex.1. Input String is 'MFT123456' --9 Characters String
Expected output should be : 'MF123456' --Restrict to 8 Characters String by removing single char 'T' and shifting all digits to left.

Ex.2. Input String is 'LCC1234567'
Expected output String = 'L1234567'

I tried to achieve this using below logic. But it's not working for 2nd example and looking for dynamic solution for this.

SELECT CASE WHEN LENGTH('MFT123456') > 8
THEN CASE WHEN SUBSTR('MFT123456', 1,3) = 'MFT' THEN SUBSTR('MFT123456',1,2) || SUBSTR('MFT123456',4)
END
END AS PIN;

and Connor said...

We can look for the first digit position and compare that to the length

SQL> with t as
  2  ( select  'MFT123456' x from dual union all
  3    select  'LCC1234567' x from dual )
  4  select
  5    substr(x,1,7-length(x)+regexp_instr(x,'\d'))||  regexp_substr(x,'\d+') str
  6  from t;

STR
--------------------------------------------------------------------------------
MF123456
L1234567

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

strings shorter than 8 characters

Mikhail Velikikh, February 14, 2020 - 2:28 pm UTC

Can your strings be shorter than 8 characters?

SQL> with t(x) as (
  2    select 'MFT123456' from dual union all
  3    select 'LCC1234567' from dual union all
  4    select 'A123456' from dual union all
  5    select 'B52' from dual)
  6  select x,
  7         substr(x,1,7-length(x)+regexp_instr(x,'\d'))||regexp_substr(x,'\d+') str,
  8         regexp_replace(x, '^(\D*)(\D{'||greatest(length(x)-8,0)||'})(\d+)$', '\1\3') str2
  9    from t;

X          STR          STR2
---------- ------------ ------------
MFT123456  MF123456     MF123456
LCC1234567 L1234567     L1234567
A123456    A1123456     A123456
B52        B5252        B52

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

strings shorter than 8 characters

More to Explore

PL/SQL demos

PL/SQL docs

Left padded String based on sub-string length

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

strings shorter than 8 characters

More to Explore

PL/SQL demos

PL/SQL docs