I grew up on James Island, but it’s pretty commercial now. I think it got incorporated into Charleston proper, so you’d have to pay city taxes, and I think the houses are more expensive. Still a very nice place to live, and growing fairly decently. Folly Beach is still pretty good, but watch out for the undertow - you don’t want to drown or end up halfway down the coast in a little kayak.
I would actually recommend LIVING on John’s Island, which is a little further out, but so very beautiful. Cheaper, because it isn’t part of Charleston city, and lots of open properties and marshland access for boating or fishing.
If you don’t have to work in the city proper, or don’t mind commuting, Edisto Island is also beautiful, and there are lovely places all up and down the 17 corridor which are going to be a lot cheaper than living in the city or any of the nearby sections like Mt. Pleasant. (Don’t get me wrong, MP is BEAUTIFUL, but a little pricy.)
Orangeburg is also close enough to get to the beach pretty regularly, and it’s a lot cheaper as well.
I would HIGHLY suggest coming down to stay for at least a week or so in the high summer - sometime in late August is a good time. Go outside in the daytime and imagine yourself cutting your grass, or trying to weed a garden or wash your car. If you don’t pass out or feel the need to find a respirator, you should be ok.
I would not suggest living anywhere in the midlands - Columbia has absolutely nothing to recommend it - it’s hotter and more humid than the beach, there is no breeze, and no culture.
If Charleston feels a little too hot or muggy, consider Ashville in NC, or Greenville SC, both have pretty decent culture and city-life. They are also much less conservative than a lot of the nearby areas. Greenville is about 5 hours from Charleston, and Ashville is closer to 7, so beach trips would probably be overnighters or weekends, but there are lovely lakes and rivers in the upstate also.
Be prepared for Blue Laws (no booze on Sunday, shops don’t open at all on Sunday or at least not until after 1 or 2 pm), and for overly inquisitive southern manners.
In other words, be prepared for a proper introduction to the neighbors to include an interrogation as to where you go to church and whether you’re related to anyone here. It behooves you to find some ancestor somewhere that lived in Charleston or nearby, and indicate that you are inspired by said ancestor and wish to emulate him. Your neighbors will appreciate your effort to belong.