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

Breadcrumb

Easter

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)

We're not taking reviews currently, so please try again later if you want to add 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.