Skip to Content

How to bring value before special characters

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.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Aug 26, 2015 at 10:04 AM

    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


    job.PNG (23.2 kB)
    Add a comment
    10|10000 characters needed characters exceeded

    • 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

  • author's profile photo Former Member
    Former Member
    Posted on Aug 25, 2015 at 05:42 AM

    Hi Sri,

    Use Combination of SBSTR and INDEX.

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

    Regards,

    Sandeep

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 25, 2015 at 06:58 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 25, 2015 at 08:00 AM

    Another way would be to replace unwanted characters with blank,

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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.