cancel
Showing results for 
Search instead for 
Did you mean: 

Incorta formula

Ram
Cosmonaut

 

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

Segment1formula_required
9211-50829211
86062C-C16-SPS86062C-C16
U2653A-ACFU2653A
U2652A-OC-INCDOCU2652A-OC

Note : Basically it has to print the characters before the last '-'

2 REPLIES 2

michael_ross
Employee
Employee

Hi @Ram 

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
	))
)

 

michael_ross_0-1654693623879.png

 

Ram
Cosmonaut

Hi Mike - It worked like a gem, Kudos and thanks !!