on 08-10-2015 7:07 PM
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
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!
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;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
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.