cancel
Showing results for 
Search instead for 
Did you mean: 

search and replace a string

Former Member
0 Kudos

Hi

  I have a couple of varchar columns with values separated by commas. I would want to remove any spaces around the commas and need to place the independent values in single quotes.

2 objectives

  • need to remove any spaces (one or more) around the comma. Only around the comma and I dont want to replace the spaces otherwise.
  • Need to place the values separated by comma in single quotes

Example

Input

Column A     Column B

John               Tim  ,  Tom

Dany               112, 118

Jenny              In Town

Output

Column A     Column B

John               'Tim','Tom'

Dany               '112','118'

Jenny              In Town

Am using BODS designer version 14.2.2

Cheers!

Accepted Solutions (1)

Accepted Solutions (1)

chethan_lingaraju
Active Participant
0 Kudos

Solved:

$TxtOut = '';

If (index($Text ,',',1)  > 0)

begin

    $i =1;

    While (word_ext( $Text ,$i,',') <> $Text )

    begin

        if ($i <> 1) $TxtOut = $TxtOut || ',';

        $TxtOut = $TxtOut || '\'' || ltrim_blanks(rtrim_blanks(word_ext( $Text ,$i,','))) || '\'';

        $i = $i +1;

    end

end

else

    begin

    $TxtOut =$Text ;

end

return $TxtOut;

chethan_lingaraju
Active Participant
0 Kudos

Hi Josh, i see that this question is not marked as answered. Did it work for you?

Former Member
0 Kudos

yes it did, I used the same logic in the mapping expression rather than creating the custom function. Thank you.

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi,

The easiest way to do is split the column B into two (use word ext or substring to do that) so you will get two values one with comma and other without comma. Use ltrim_blanks_ext(rtrim_blanks_ext(without comma)) and concatenate with the first value (with comma).

Thanks,

Arun