Transforming string data in a SQL Server database
I need to convert some data stored as varchar in a SQ开发者_Go百科L 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?
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)
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')
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.
精彩评论