Transforming string data in a SQL Server database

Question

I need to convert some data stored as varchar in a SQL Server database that I want to change from CamelCase to delimiter-separated. I know how to do this trivially in C# but not T-SQL. Any ideas?

Answer 1

I haven't SQL Server on hand to try, but you need a loop testing for:

CODE(SUBSTRING (xx, thatpositions, 1)) BETWEEN 64 and 89

Then insert your delimiter:

SUBSTRING (xx, 1, thatpositions -1) + delimiter + SUBSTRING (xx, thatpositions, 8000)

Answer 2

If you have C# code to do this, and are using SQL Server 2005+, you can expose the .NET code as a TSQL function (or stored procedure) via SQLCLR. Check that you're allowed -- most don't, for sake of support and/or great potential for misuse. Also be aware that SQL Server 2005 uses .NET 2.0 while SQL Server 2008 (incl. R2) is .NET v3.5.

This article caters to enabling SQLCLR (doesn't require a restart), building, deploying & using a SQLCLR for regex support.

It's my preference to use TSQL whenever possible though.

Answer 3

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Alter FUNCTION dbo.GetDelimitedString 
(   
    @CamelCaseString nvarchar(max) 
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @Result_String nvarchar(max)
    Select @Result_String = @CamelCaseString

    DECLARE @pos INT
    DECLARE @DelimiterCount INT
    Select @Pos = 2
    Select @DelimiterCount = 0        

    WHILE @pos <= DATALENGTH(@CamelCaseString)
    BEGIN
        IF ASCII(SUBSTRING(@CamelCaseString, @pos, 1)) BETWEEN 65 AND 90

        BEGIN
            set @Result_String = STUFF(@Result_String,@Pos + @DelimiterCount,0,',')
            Set @DelimiterCount = @DelimiterCount + 1
        END
    SET @pos = @pos + 1;
    END

    RETURN @RESULT_STRING
END

And you can use this function -

select dbo.GetDelimitedString('TestStringInCamelCase')