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