I am planning to migrate from my Sony DSC-HX1 bridge camera (28-560mm) to Canon EOS 800D.
Now I had learned that 'x' based zoom values are not applicable to DSLR's and I am lost with all the mathematical calculations, so my questions is:
What lens will provide me all capabilities of my earlier bridge camera like shooting Macro, landscape, portraits, zoom range etc. effectively?
The entire point of an interchangeable lens system camera is to allow you to use different lenses that are better or even great at one thing but unsuitable for other things. Fixed lens cameras force you to use a single lens that is mediocre or worse at a lot of things but better at nothing.
The best lenses are all prime lenses. That means a single focal length. No.Zoom.At.All. They're really good when they provide the field of view and other characteristics you need. This is because they can be optimized to do one thing at one focal length. A good flat field 100mm macro lens is different from a good 85mm, 105mm, or 135mm portrait lens. But such specialized lenses are not always very flexible, so you need a lot of them for various different things. Some are pretty good for not much money (e.g. EF 50mm f/1.8 STM @ $120). Others are incredibly good for a boatload of cash (e.g. EF 400mm f/2.8 L IS II @ $10K). Most fall somewhere in between.
Short ratio zoom lenses, that is zoom lenses with a less than 3X difference between their longest and shortest focal length, can also be very good. But the best ones cost a lot. A lens like the Canon EF 24-70mm f/2.8 L II runs around $2K and can match the image quality, if not the maximum aperture, of a $120 EF 50mm f/1.8 STM. It's also built a bit better and can shoot at 24mm (with near the same IQ as a mid-priced 24mm prime) and 70mm and anywhere in between.
When you move outside of the 3x limit is when image quality really starts to noticeably go down. Some 4-5X zoom lenses that fall entirely in the telephoto range can be pretty good. But when you start trying to design a lens that goes from wide angle to telephoto and covers a 5X-10X or more zoom range, that is when it really starts getting difficult to keep it affordable and manageable with regard to size and weight and still provide excellent image quality. You'll usually get better image quality and spend less buying something like an 18-55mm and a 55-250mm pair of zoom lenses than you would get with an 18-200mm 'all-in-one'.
The other thing you must consider is that larger sensors require larger lenses to get the same field of view. Your 28-560mm superzoom is really a 5-100mm lens in front of a sensor that is 5.6X smaller in linear measurements and covers an area less than 1/30 the size of a FF sensor. It also cover less than 1/12 the area of the APS-C Canon 800D. There are tradeoffs with low light ability, noise level even when shooting in daylight, image sharpness, particularly at the telephoto end, etc. that were made to give you that "20X zoom".
Will you get better image quality with an 800D and something like a Tamron 18-400mm f/3.5-6.3 Di II VC compared to your current Sony? You probably will.
But you'd get even better quality spending that $650 you'd need for the Tamron 18-400mm towards a collection of other lenses. Something along the lines of the 18-55mm kit lens for about $100 more than the body only, an EF 55-250mm f/4-5.6 STM for about $300 (or the older EF 55-2550mm f/4-5.6 IS II that is getting harder to find new for around $100-150), and an EF 50mm f/1.8 STM for about $120. You'd still be $100+ bucks ahead. If you really want a little more focal length then get a 70-300mm for about $500 new (or $350 used) instead of the 55-250mm (avoid any version of the EF 75-300mm, it's the worst lens Canon makes). 300mm on your APS-C Canon gets you to 480mm FF equivalent FoV.
Anything past 300mm for an APS-C or larger sensor camera is really going to start costing some money or the maximum aperture and/or image quality is going to start to suffer. There are some 150-600mm zooms from Tamron and Sigma for a little over $1,000 that some folks like. They have fairly narrow maximum apertures of f/6.3 so they are pretty good in bright light but aren't very useful for sports/action under lights or for wildlife in the early morning and late afternoon when most wild animals are most active.
If you decide you just have to have that kind of reach then consider another newer and hopefully better 'superzoom' bridge camera rather than a DSLR. Or be prepared to take out a second or third mortgage on your house.
I believe the closest you can currently get to this is the Tamron 16-300, which, due to the 800D's crop factor of 1.6, is a ff equivalent 26-480.
Edit: there's now the 18-400mm f/3.5-6.3 Di II VC HLD, with an ff equivalent of 29-640, a factor of over 22!
Note that superzoom lenses like this inevitably produce poorer image quality than more specialized lenses. And indeed, one of the advantages of a ILC (interchangeable lens camera) is the fact that you are not stuck with a single all-purpose lens.
The Tamron will not really work as a macro lens, its closest focusing distance is only 39cm. For portrait, you might want a wider aperture than the f/3.5-6.3 of this lens.
If you want a good image quality, forget about 30x zoom. Also, I believe there isn't a 28-560mm equivalent for DSLRs at all. If you want a single lens to cover all your needs, I'm afraid the answer would be - stick to your current camera.
