Quick T-SQL functions #1

Written by

Posted on

Whilst messing around with some T-SQL code for parsing various bits of text I found I needed a function to convert string in upper case format (e.g. “THIS IS AN EXAMPLE”) to camel case, capitalised first letter and no spaces. So we’d end up with “ThisIsAnExample” as the resulting string output.

I thought I’d give the SQL Server 2017 string functions STRING_SPLIT and STRING_AGG a bit of a whirl since I’d not needed to use them for anything new yet. Turns out they helped make something I thought would be a bit of a faff into just a few lines 🙂

Its very basic but does the job and could easily be extended to perform more complex conversions with complicated strings.

CREATE FUNCTION [dbo].[fnConvertStringToCamelCase] (
	@string NVARCHAR(MAX)
	,@separator NCHAR(1)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
	DECLARE @result NVARCHAR(MAX) = (
		SELECT
		STRING_AGG(Word, '') -- glue rows back together into a single value

		FROM (
			SELECT
			UPPER(LEFT(value, 1)) + RIGHT(LOWER(value), LEN(value) - 1) AS Word

			FROM
			STRING_SPLIT(@string, @separator) -- split string into rows
		) s
	)

	RETURN @result
END
%d