If you are using the Content type Hub in SharePoint to publish content types across the entire SharePoint Farm / Tenant, there is one thing you must never do. Never, ever, use the asterisk (*) in a site column. Here is the story behind this statement. Then, I will give the explanation as to why.
The Story
I was working with a client a while back when I stumbled over this issue. They were creating multiple corporate Content Types in the content type hub to ensure uniformity with the data that was being captured for electronic assets that used OCR technology to place them into SharePoint libraries. To identify those Corporate Content Types the used an asterisk (*) as a visual cue. It looked something like: Contract Value*. Simple… right? At first sight, yes.
The Problem
This was fine the first time the content type(s) were published. Everything seemed to be fine until the republishing of the content types took place. An employee noticed something odd took place after the republishing. There were two columns of Contract Value*. Which one was the correct one? Why were there two columns to begin with? Some deleted the second one manually. That fixed it… right? No, the next republishing, the same issue happened. For those who did not delete the duplicated column now had three columns.
What was happening? I came to find out from the client this situation had been going on for 2 years. A ticket was opened two years prior to my looking at the situation. After reopening the ticket, I began to work with Microsoft, learning they closed the ticket with no resolution as they could not figure out the issue.
The Enlightenment
After numerous cups of tea and trips to the restroom over a number of days something came to me. SharePoint uses SQL server as it’s backend. Ok. Fine. Wait. Gears are turning in my head.
An asterisk in SQL server is also known as a wildcard. This could very well mean everything found after the first part. In the case of the columns with a asterisk after it, Contract Value* would most likely be grabbing anything that is after that column name. Flags, backend information, dates, whatever it may be following it, it (what does this it stand for?) was using it (and this it?) to name the new columns when it republished. In a situation like this Microsoft had to come up with specialized PowerShell scripts just to clear the offending columns, rename them at the core, or change them completely.
The Bottom Line
Do not use the asterisk in column headers. They could very well cause you some trouble, though Microsoft I am sure has continued design counter measures, so this doesn’t happen. I would recommend being safer than sorry. The level of the mess that it potentially unleashes goes too deep to allow this issue to continue unchecked.