I had an interesting problem recently in which I needed to take a set of columns defined in a particular manner and turn them into values for a table. I also needed to create cleaned-up names for these columns, dropping off a suffix and splitting the camel case into separate words. Doing this is actually pretty easy with a tally table, as the code below shows. I have broken it out into two common table expressions to make it easier to read.
Note that I assume you have a tally table called Numbers with a single column by the name of Number in the current database. If you don’t, creating one is easy and useful.
WITH Questions AS
sc.name as ColumnName,
SUBSTRING(sc.name, 1, LEN(sc.name) - 3) as SplitColumnName
INNER JOIN sys.tables st ON sc.object_id = st.object_id
INNER JOIN sys.schemas ss ON st.schema_id = ss.schema_id
INNER JOIN sys.types styp ON sc.system_type_id = styp.system_type_id
ss.name = 'MySchemaName'
and st.name = 'MyTableName'
and sc.name like '%IND'
and styp.name = 'char'
SUBSTRING(q.SplitColumnName, n.Number, 1) as Letter,
CASE WHEN BINARY_CHECKSUM(SUBSTRING(q.SplitColumnName, n.Number, 1)) = BINARY_CHECKSUM(LOWER(SUBSTRING(q.SplitColumnName, n.Number, 1))) THEN 0 ELSE 1 END AS IsUppercaseLetter
CROSS JOIN Questions q
n.Number <= LEN(q.SplitColumnName)
CASE WHEN svi.IsUppercaseLetter = 1 AND svi.Number > 1 THEN ' ' ELSE '' END + svi.Letter
svi.ColumnName = sv.ColumnName
FOR XML PATH('')
), 1, 0, ''
) as SplitColumnName
The first common table expression gets the valid set of columns. Searching through a particular table and whose column name ends with “IND” (an indicator that this is a question, at least in my case) and whose type is character (again, an indicator of a question in my case), we get the column name. I also remove the IND suffix because I won’t need that for the final question name.
The second common table expression then uses a tally table to break out each letter. We use the BINARY_CHECKSUM function to compare whether this letter is uppercase or lowercase here as well, so we know where we need to put spaces in.
The final portion of this query uses STUFF and FOR XML PATH to re-stuff all of these values into one column, and if I have an uppercase letter after the first character position, I first put in a space. This guarantees that my camel-case question gets split out.