• Questions
• Left padded String based on sub-string length

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

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.