Is it one of these spells that are surprisingly bad, or surprisingly useful?

After flaming sphere, I'm willing to give many a thing the benefit of the doubt 🧐

For your standard spell-caster, I think it's awful. It requires your action and a melee touch attack, heals hp instead of giving temporary hp, and you've got to concentrate on it. If you're not an armored caster, you've little hp and ac, you need to get out of melee as opposed to hanging around and trying to get some hp back. In this game, you'll most certainly lose concentration, do ineffectual damage, and likely get killed in the process.

For a 'melee caster', it's a little better, but still largely pointless. You'll use your action to do crap damage, get half that back as healing, if you even need the healing at the time. I guess if you don't have extra attack, which execution of this spell will interfere with, it's not as bad. But, in the end, you'll probably just lose concentration on it, and do little or nothing to improve your situation. And all this mediocrity for the cost of a 3rd level spell slot. Surely one can think of something better to do with a 3rd level spell slot. Perhaps the absolutely busted Haste spell?

Yeah, it’s not a spell I’ve found a decent use case for and find it a struggle to justify even as a pick for RP reasons. The fact that it can be used to keep doing damage without use of spell slots (if concentration can be maintained) might be occasionally useful for classes with a limited number of spell slots, such as a melee warlock, arcane trickster, eldritch knight or some form of multiclass. Though as Ignatius says, it’s going to be tricky to justify against an extra attack for a level 5 fighter of any sort. If sneak attack damage is implemented for the off hand in full release, might it be useful for a dual wielding multiclass rogue/spell caster or arcane trickster (though I think they would need to be level 13 to get the spell)? I’m really reaching here.

The AI has this thing where it prioritizes a target that is concentrating on a spell over just about anything. So being in melee range + concentration + need to perform touch attacks is a recipe for disaster with that spell. For it to be effective at all you would need Mirror Image Up and high AC anyway - and even then the damage is nothing special. 3d6 Necrotic with no modifier damage you will average 12 damage and heal 6- that's just not worth it. You are better off with the Githyanki + Potion of Hill Giant Strength + Sword of Justice to do 2d6 + 6 = 8-18 damage and concentrate on Tyr's protection for +2 AC - while tossing fireballs for 8d6 damage.

Some spells become stronger as characters become higher level, such as Blindness/Deafness. Maybe Vampiric touch is a better selection at level 10?

Or it could combo well with say Hypnotic pattern?

Not unless you upcast it.

Casting Time: 1 action
Range: Self
Target: A creature within your reach
Components: V S
Duration: Up to 1 minute
Classes: Warlock, Wizard
The touch of your shadow-wreathed hand can siphon life force from others to heal your wounds. Make a melee spell attack against a creature within your reach. On a hit, the target takes 3d6 necrotic damage, and you regain hit points equal to half the amount of necrotic damage dealt. Until the spell ends, you can make the attack again on each of your turns as an action.
At Higher Levels. When you cast this spell using a spell slot of 4th level or higher, the damage increases by 1d6 for each slot level above 3rd.

The only real appeal of this spell I can see is if you have a couple of companions who love to spam (upcast) Hold persons for you to crit-fish.

Or you're 12th level with 70hps, badly wounded and surrounded by chickens. 1 minute later, you've healed 30d6/2 (52hps) and whole lot of desiccated chooks.