How to bring value before special characters

[vas_y]

Hi friends,

Can you plesae help me on below scenarios.

I have value 'ABC_D_E_x1' and wants to bring value before '_x1' i.e., 'ABC_D_E'

I tried using word_ext function but couldn't get the correct value since the value has separator '_' and have repeats multiple times and it brings partial value based position and separator.

can we use any other function that brings the value 'ABC_D_E'

I would appreciate your earliest response.

[Former Member · Accepted solution]

Hi there,

I have created a custom function that accepts an input text and returns the text without the _xn, where n can be whatever number between 1 and a hardcoded value. I've set this up till 1000, but you can change it to a higher number if you think you can reach this number.

you can call the function in a script like

print( extract('ABC_D_E_x9'));

OR

in the mapping editor.

$v=1000;

while ($v!=0)

begin

$v=$v-1;

$pText = replace_substr($pText,'_x'||$v,'');

end

return $pText;

The parameters and variables are defined like this:

Parameter: $pText

Datatype: varchar(100)

Type: Input

Variable: $v

Datatype: int

Variable: $vLen

Datatype: int

Result:

Please mark this question as answered.

Regards,

Bogdan

[vas_y · Accepted solution]

Thank you Bogdan and your logic works and also I tried by using below and works what I expected.

print( word_ext(substr('AAA/ABC_D_E_X1',0,length('AAA/ABC_D_E_X1')-length(word_ext( 'AAA/ABC_D_E_X1',-1,'_'))-1)),-1,'/');

Thanks you for your kind support and appreciated your help.

Thanks,

Sri

[chethan_lingaraju]

Another way would be to replace unwanted characters with blank,

print(replace_substr('ABC_D_E_x1','_x1',''));

[Former Member]

Hello Sri,

I hope this is what you are looking for:

substr('ABC_D_E_x1',1,index( 'ABC_D_E_x1','_x1',1)-1)

Regards,

Sandhya B S

[vas_y]

I tried with this combination but didn't work since 'x1' is dynamic value that could be ***_x1 or ****_x2 or ***_x3 or etc....

do you have any other logic?

[Former Member]

Hi Sri,

Use Combination of SBSTR and INDEX.

Ex. SUBSTR(Column_name,1,index(Column_name,'_x1',1))

Regards,

Sandeep

[vas_y]

I tried with this combination but didn't work since 'x1' is dynamic value that could be ***_x1 or ****_x2 or ***_x3 or etc....

do you have any other logic?

[Former Member]

Hi Sri,

Then try replacing '_x1' by '_x?'

Regards,

Sandeep