06-08-2022 04:00 AM
Hi Team - Wondering whether below oracle Sql can be achieved using Incorta formula?
segment1 is the input
formula_required is the output.
select segment1, substr(segment1,1,(length(segment1)-instr(reverse(segment1),'-'))) AS formula_required from dual;
Sample input and output
Note : Basically it has to print the characters before the last '-'
Solved! Go to Solution.
06-08-2022 06:20 AM - edited 06-08-2022 06:22 AM
I was able to replicate this functionality using the substring and findLast functions in the formula builder. This formula performs a substring starting at position 0, and then uses the findLast function to find the position of the last dash in the string, and ends the string there.
The example below shows the formula in an insight, but the same formula can be used in the Schema or Business Schema areas if you want to persist the results for others to use without having to type the formula every time in an insight.
I included your examples in the sample data, and I added an extra example with three dashes so you could see that it still works with more than two dashes.
substring( Community_Questions.StringQuestion06082022.Sample_Data, 0, (findLast( "-", Community_Questions.StringQuestion06082022.Sample_Data )) )
06-09-2022 10:19 PM
Hi Mike - It worked like a gem, Kudos and thanks !!