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.


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).

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.