on 08-25-2015 5:35 AM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Another way would be to replace unwanted characters with blank,
print(replace_substr('ABC_D_E_x1','_x1',''));
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Sri,
Use Combination of SBSTR and INDEX.
Ex. SUBSTR(Column_name,1,index(Column_name,'_x1',1))
Regards,
Sandeep
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.