I want to perform select statement in oracle database but not get the expected result. I have column value with 21 character however for 0 value it display empty space. I'm only interested to take data from position 7-17 character. For those empty value in position between (13-17) should replace with 0 .
Try to using this query
SELECT
SUBSTR(name, 7, 11) as Trim_Value1,
LPAD(Trim_Value1, 5, '0') as Final_Value,
type
FROM Table1
expected output :
A1777M06014
A5283S34811
A3990B00014
Example data:
V66001A1777M 6014ZU1C
V66001A5283S34811Z100
V66001A3990B 14Z100
Kindly advise which suitable function i can use for get as my expected result.
This may not be the cleanest way -- I don't know the Oracle function library incredibly well. It appears that you always have a Z character somewhere after the 13 position. The key part of the operation is handling that variability. Hardcoding that information might give you something like this:
substr(name, 7, 6) ||
lpad(trim(leading ' ' from substr(name, 13, instr(name, 'Z', 13) - 13)), 5, '0')