Replacing double digit numbers

Question

I have column with numeric values that I need to replace into text values.

I used the sql statement:

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(loanstatus, '1', 'Apple'), '2', 'Banana'), '3', 'Citrus'), '4', 'Deer'), '5', 'Elephant'), '6', 'Fruits'), '7', 'Grey'), '8', 'Hope'), '9', 'Island'), '10', 'June'), '11', 'Kate'), '12', 'Live'), '13', 'Moon')

I've tried using Double Quotations (") in place of Singles ('). I've also tried using String('1') and String("1").

Values 1 to 9 works fine. The problem is if the insert value is "11" then the output is AppleApple If "12" then "AppleBanana" when it should come out as "Kate" for 11 and "Live" for 12.

Answer 1

The doubling up is because of your nested parenthesis. Processing will start with the inner-most pair and work outwards. In this case the innermost replacement is 1 -> Apple meaning 11 -> Apple1 -> AppleApple.

Reverse the nesting, starting with the longest source value and work outward. Better still, put the mappings in a separate table and join to it (if Foxpro supports join-like syntax).