First, developers certainly do write (select a + ' ' + b + ' ' ... ) all the time. It's not a great idea, and there are problems, but that code exists. Testing, and maintaining tests over time, help to show developers where their code has issues, and prevent regressions. The point here is to have repeatable testing.
The case chosen is for names, which are complex. There are many more rules that could be applied, but to keep this relatively simple, I just chose a few. I didn't cover every case, because this is a simplified model. You certainly could expand this to check for existing periods or other issues. In fact, rather than just complaining about this piece not meeting your needs, perhaps you'd extend this to include another case, or explain how you would deal with the existing period? Remove it and always put it back? Check and make a decision on adding it? There are multiple implementations that could work, and one is likely more efficient than others, but how do you know that the code you've written works?
You wrote "I can write a procedure and test it on a small set of data easily". Sure. You can. It's definitely going to be faster for you if you haven't built testing skills. However, I can assemble this test quickly. Most of the test would include the same sample data I want. However, as you noted, I didn't include a case with a middle name that has a period already. Would that be in your data set? Maybe.
One of the issues with much code is that developers are busy and they focus on the problem from their view, which is often a limited view. Very few of us have time to explore every possibility. If you deployed this code (not saying you would), and QA reported this shows that "Steve A. Jones" is returned as "Steve A.. Jones", you'd know there's a bug. You could modify your code and assemble another data set, or run queries, but as the list of inputs grows, are you positive you check every output row correctly? I would argue that you, and most developers, don't perform that diligence as well as they could because we make mistakes as humans.
With this test, I can easily add another row of data to the rest (both the input, and the expected output) and then just exec tsqlt.run my test and it will check not only the new case, but every previous one as well. If I add more complex logic for Van Hewitt or MacDonald as a last name or multiple middle names, I can prevent regressions by having a formal test.
Not only can I prevent regressions, but I also prevent future individuals from changing the code without having a test run at some point (manual, CI, etc.) that validates previous cases we know about.
It seems heavyweight, which is one reason I didn't try to cover every case or make this a 100% solution. However, once you start to write these tests, they are fairly easy to put together quickly. I often need the input test data anyway, and the formal test makes me spend a minute ensuring I explicitly document the result set I want, not relying on my focus to ensure I read a result set correctly every time.