Skip to Main Content
  • Questions
  • Left padded String based on sub-string length

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Connor McDonald

Thanks for the question, Shyam.

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

Answered by: Connor McDonald - Last updated: February 14, 2020 - 12:35 pm UTC

Category: PL/SQL - Version: 4.2

Viewed 100+ 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 we 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


and you rated our response

  (1 rating)

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

Reviews

strings shorter than 8 characters

February 14, 2020 - 2:28 pm UTC

Reviewer: Mikhail Velikikh from Dublin, Ireland

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

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.