How to convert records based on certian contents of records

[ambasta]

Hi All,
My requirement is to convert the data based on certain conditions.I am explaining the problem using an example.
If data is like 12345$ABC6789 then populate data as 12345A6789
if data is like 12$BCD3456789 the populate data as 12B3456789
if data is like 1234567$CDE89 the populate data as 1234567C89.
these four digits ($ABC,$BCD,$CDE) can be at any position in the source data.
Can anyone of you please help me on this...
Thanks in Advance..

[DSguru2B]

Do this:

Code: Select all

Field(in.Col, "$",1):Field(in.Col,"$",2)[1,1]:Field(CONVERT('ABCDEFGHIJKLMNOPQRSTUVWXYZ','',Upcase(in.Col)),"$",2)

Get everything before the dollar sign, concatenate it to the first character of everything after the $ sign. Then finally get rid of all your alphabets and concatenate everything after the dollar sign. Pretty much what the above code is doing.
NOTE: If there are other characters, excluding $ sign, that will be present along with alphabets, add them to the list in the CONVERT() function.

[ambasta]

Do this:

Code: Select all

Field(in.Col, "$",1):Field(in.Col,"$",2)[1,1]:Field(CONVERT('ABCDEFGHIJKLMNOPQRSTUVWXYZ','',Upcase(in.Col)),"$",2)

Get everything before the dollar sign, concatenate it to the first character of everything after the $ sign. Then finally get rid of all your alphabets and concatenate everything after the dollar sign. Pretty much what the above code is doing.
NOTE: If there are other characters, excluding $ sign, that will be present along with alphabets, add them to the list in the CONVERT() function.

Thanks a lot for your kind suggession..actually i am not sure why we are taking substring[1,1].Will you be kind enough to explain the logic using any example.Thanks in advance..

[DSguru2B]

If data is like 12345$ABC6789 then populate data as 12345A6789
if data is like 12$BCD3456789 the populate data as 12B3456789
if data is like 1234567$CDE89 the populate data as 1234567C89.

Look at your final data. It contains the first alphabet right after the $ sign. Thats what the substring is for.

[ArndW]

Ambasta, there is no need to quote entire messages when replying.

DSGuru2B's logic is succinct:

Take the whole string before the first $ sign
Append the single character after the first $ sign
Remove alpha characters from the remainder and append.

I might suggest changing that a bit to read

Code: Select all

Field(in.Col, "$",1):Field(in.Col,"$",2)[1,1]:Field(in.Col,"$",2)[4,9999]

since your delimiting string is always 4 chars long.

And for efficiency it might be best to assign a stage variable to the output of Field(in.Col,"$",2).

[ambasta]

All the above example of records is only for single column.how do i write the if-else condition.If data contains $ABC then replace it with A else if data contains $BCD then replace it with B else if data contains $CDE then replace it with C and so on....

[ray.wurlod]

You've missed the point. You don't need to treat them separately, because they follow a common pattern, namely nnnnnn$aaaannnnn. As was described - documented - in the response by DSguru2B, the expression takes:

• everything to the left of the $ character {Field(x, "$", 1, 1)}
  
  the first character of (everything to the right of the $ character) {Left(Field(x, "$", 2, 1), 1)} - which he gave as Field(x, "$", 2, 1)[1,1]
  
  then removes all the alphabetic characters from (everything to the right of the $ character) [Convert("ABCDEFGHIJKLMNOPQRSTUVWXYZ", "", Upcase(Field(x, "$", 2, 1))}
  
  finally concatenating the three intermediate results to form the desired answer

[DSguru2B]

I guess my explanation was'nt clear enough. Ray's explanation should put you to rest and answer your doubts.

[ray.wurlod]

If you're happy with the explanation, please mark the thread as Resolved. Otherwise please indicate what it is you don't follow.