How to make first letter upper case in every word without using functions in mysql?

Question

I have a string in mysql like this - "my_report_id". I would like to remove all "_"s and make all first letters in every word capital. The final result should be - "MyReportId". Is it possible to achieve this in mysql without using functions/procs? I don't want to use functions/procs, because of several reasons:

I don't have the permissions to create them;
The query I am building should be used in a lot of databases and I don't want to create the same function in every DB on which I am going to run the query;

I already saw a lot of solutions to my problem with the same function on several places over the internet, but as I already wrote I don't need functions.

MY TASK: Basically what I am trying to achive is to parse mysql tables to C# model classes for ASP.NET MVC. I need to create this "parser" in mysql and I found this solution (the second answer with provided code) very useful - Create C# classes based of MySQL table I started from the code from the link and updated it to parse nullable and unsigned types, too. I did something like this:

SELECT CONCAT('[Table(Name = "myTableName")]') UNION
SELECT CONCAT('public class ', "myTableName",'{') UNION
SELECT CONCAT('[Column(Name = "', COLUMN_NAME, '")] \n public ', 
CASE IS_NULLABLE
    WHEN 'NO' THEN
    CASE WHEN COLUMN_TYPE LIKE '%unsigned%' THEN myTypeNonNullableUnsigned.cSharpType
      ELSE myTypeNonNullable.cSharpType
    END
    WHEN 'YES' THEN
      CASE WHEN COLUMN_TYPE LIKE '%unsigned%' THEN myTypeNullableUnsigned.cSharpType
    ELSE myTypeNullable.cSharpType
    END
END, ' ',
CONCAT(UCASE(LEFT(COLUMN_NAME, 1)), SUBSTRING(COLUMN_NAME, 2)),
' {get;set;}')
FROM INFORMATION_SCHEMA.COLUMNS c
JOIN(
SELECT 'char' AS sqlType ,'string' AS cSharpType UNION ALL
SELECT 'varchar', 'string' UNION ALL
SELECT 'datetime', 'DateTime' UNION ALL
SELECT 'text', 'string' UNION ALL
SELECT 'tinyint', 'bool' UNION ALL
SELECT 'int', 'int' UNION ALL   
SELECT 'longtext', 'string' 
) myTypeNonNullable ON c.DATA_TYPE LIKE myTypeNonNullable.sqlType
JOIN( 
SELECT 'char' AS sqlType ,'string' AS cSharpType UNION ALL
SELECT 'varchar', 'string' UNION ALL
SELECT 'datetime', 'DateTime?' UNION ALL
SELECT 'text', 'string' UNION ALL
SELECT 'tinyint', 'bool?' UNION ALL
SELECT 'int', 'int?' UNION ALL  
SELECT 'longtext', 'string'
) myTypeNullable ON c.DATA_TYPE LIKE myTypeNullable.sqlType
JOIN(
SELECT 'char' AS sqlType ,'string' AS cSharpType UNION ALL
SELECT 'varchar', 'string' UNION ALL
SELECT 'datetime', 'DateTime' UNION ALL
SELECT 'text', 'string' UNION ALL
SELECT 'tinyint', 'bool' UNION ALL
SELECT 'int', 'uint' UNION ALL  
SELECT 'longtext', 'string' 
) myTypeNonNullableUnsigned ON c.DATA_TYPE LIKE myTypeNonNullableUnsigned.sqlType
JOIN(
SELECT 'char' AS sqlType ,'string' AS cSharpType UNION ALL
SELECT 'varchar', 'string' UNION ALL
SELECT 'datetime', 'DateTime' UNION ALL
SELECT 'text', 'string' UNION ALL
SELECT 'tinyint', 'bool' UNION ALL
SELECT 'int', 'uint?' UNION ALL 
SELECT 'longtext', 'string' 
) myTypeNullableUnsigned ON c.DATA_TYPE LIKE myTypeNullableUnsigned.sqlType
WHERE TABLE_SCHEMA='myTableSchema' AND TABLE_NAME='myTableName'
UNION
SELECT '}';

The only thing left is to make the names of the properties to c# naming convention.

Maybe it's not very pretty, but for the time I have is the best solution I could come to. Any help will be greatly appreciated.

why you can't use functions? there is a perfect one here http://joezack.com/2008/10/20/mysql-capitalize-function/ — faby, Dec 04 '14 at 14:32
Thank you for the answer. I have already seen this function, but I have to do it in a different way. See my question, I wrote there some of the reasons I don't want to use funcs. — Viktor Georgiev, Dec 04 '14 at 14:46
Sadly you really **cannot** do it. A poor man Title Case for C# identifiers may be done with a regexp but unfortunately REGEXP in MySQL supports matching but not replacing...so even for this you need a function (and in that case you'd better write a MAKE_VALID_IDENTIFIER() UDF. Well...AFAIK you can't (in MySQL) but there is no reason you don't **post-process generated C# source file** before you compile it... — Adriano Repetti, Dec 04 '14 at 14:52
Adriano Rapetti, if I somehow match the letter after every "_", I will easily replace it with a mysql standard function REPLACE(). After that I already have the solution to make make the very first letter capital. Or am I wrong? — Viktor Georgiev, Dec 04 '14 at 15:00

score 0 · Accepted Answer · answered Jan 15 '15 at 12:36

This is a very, very ugly solution, but it worked pretty well without the use of functions:

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(COLUMN_NAME, '_a', 'A'), '_b', 'B'), '_c', 'C'), '_d', 'D'), '_e', 'E'), '_f', 'F'), '_g', 'G'), '_h', 'H'), '_i', 'I'), '_j', 'J'), '_k', 'K'), '_l', 'L'), '_m', 'M'), '_n', 'N'), '_o', 'O'), '_p', 'P'), '_q', 'Q'), '_r', 'R'), '_s', 'S'), '_t', 'T'), '_u', 'U'), '_v', 'V'), '_w', 'W'), '_x', 'X'), '_y', 'Y'), '_z', 'Z')

Parsing the letters this way, makes it possible to follow the standard naming convention in C#.

How to make first letter upper case in every word without using functions in mysql?

1 Answers1