Replacing characters in a string

[#1]

I have sequence column with the following values : 3,1,1,0,4

I like to replace the numbers with characters

0=A

1=B

3=C

4=d

I manage to replace 1 character using the “replace”, but I didnot manage to replace the entire string with the information that I needed.

I will appreciate your help.

T.

[#2 · Accepted solution]

Hi tkalimi. Depends on what the field contains exactly.

Would values in that field only contain just one number as the whole value? EG

ID,Custom_Field

1, 3

2, 1

3, 1

4, 0

5, 4

If so, you could create a mapping table that maps those numbers to the letters specified. Alternately you could use a formula on the character number to change it to those numbers.The function to return the ascii number is Ord().

EG load ID, ord(Custom_Field) - 64 as new_field.....

ie ord('A') - 64 = 65-64 = 1

If your string is more complex than that, eg a series of concatenated numbers 31_4 or something like that you could use nested replace() functions

EG load ID, replace(replace('custom_field','1','A'),'2','B')  as new_field...

Which one do you mean?

Erica.

[#3]

Thanks a lot,

I use the concatenated replace option.

Cheers,

T.

[#4]

You're welcome!

Regards, Erica