SQL Query: how to break column name into pieces (regexp?)

Question

I have a table with many columns. All the columns (attributes) are named using the same naming convention: thisIsColumnName, thisIsAttributeName, thisIsAlsoColumnName etc. I would like to create a query (maybe using the information_schema.columns table) which breaks all these names into pieces and produces a single list containing distinct pieces of all column names:

thisIsColumnName
thisIsAttributeName

produces

this
Is
Column
Attribute
Name

How can I do this for all my columns in a table (and maybe all tables in a database) using T-SQL? Any ideas?

look for a split function, there are many for SQl Server, then make it split on a capital letter instead of the usual ",". see: http://stackoverflow.com/questions/4098216/how-to-join-to-a-table-that-has-multiple-values-in-the-column/4098376#4098376 — KM., Nov 17 '10 at 20:05

score 1 · Answer 1 · answered Nov 17 '10 at 19:58

One way would be to write a CLR user defined function which did it for you. Here's an example where I take something like "ThisIsAColumnName" and emit "This Is A Column Name"

public static String SplitCamel( String input ) {
    return System.Text.RegularExpressions.Regex.Replace(input, "([A-Z][A-Z]*)", " $1", System.Text.RegularExpressions.RegexOptions.Compiled).Trim();
} // method::SplitCamel

See this for more information: http://msdn.microsoft.com/en-us/library/w2kae45k(VS.80).aspx

score 0 · Accepted Answer · edited May 23 '17 at 11:56

As @Chris Lively indicates a solid camel splitting function of some form is probably the best approach, but here's one query that does it all inline. Collation is obviously a consideration here for the pattern matching and in this instance I have utilised a case sensitive one in the PATINDEX function (as an aside I had to explicitly define every uppercase letter in the matching expression as [A-Z] did not return the correct results, I think that's topic for another question....)

CREATE TABLE dbo.OriginalNames 
(
    CamelCaseName        VARCHAR(30),    
)
GO

INSERT INTO dbo.OriginalNames VALUES ('thisIsColumnName')
INSERT INTO dbo.OriginalNames VALUES ('thisIsAttributeName')
INSERT INTO dbo.OriginalNames VALUES ('thisIsAnotherAttributeName')

GO

SELECT * FROM dbo.OriginalNames;
GO

WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L3)
SELECT DISTINCT(SplitNames.Value)
FROM (
SELECT  nums.n, names.CamelCaseName, LTRIM(RTRIM(SUBSTRING(names.CamelCaseName, nums.n - 1, PATINDEX('%[|ABCDEFGHIJKLMNOPQRSTUVWXYZ]%', SUBSTRING(names.CamelCaseName + N'|', nums.n, LEN(names.CamelCaseName)) COLLATE SQL_Latin1_General_Cp1_CS_AS)))) AS [Value]
 FROM   Numbers AS nums INNER JOIN dbo.OriginalNames AS names ON nums.n <= CONVERT(int, LEN(names.CamelCaseName) + 1) AND PATINDEX('%[|ABCDEFGHIJKLMNOPQRSTUVWXYZ]%', SUBSTRING(N'|' + names.CamelCaseName, nums.n, 1) COLLATE SQL_Latin1_General_Cp1_CS_AS) > 0) AS SplitNames

GO

--DROP TABLE dbo.OriginalNames

-- OUTPUT as follows
--
-- Value
-- =========
-- Another
-- Attribute
-- Column
-- Is
-- Name
-- this

SQL Query: how to break column name into pieces (regexp?)

2 Answers2