views:

572

answers:

4

I have a table with a column whose values come from an Enumeration. I need to create a TSQL function to convert these values to "Friendly Names" upon retrieval.

Examples:

 'DateOfBirth' --> 'Date Of Birth'
 'PrincipalStreetAddress' --> 'Principal Street Address'

I need a straight TSQL UDF solution. I don't have the option of installing Extended Store Procedures or CLR code.

+1  A: 

If you're using SQL Server 2005, you can write a native CLR procedure:

static string ToFriendlyCase(this string PascalString)
{
    return Regex.Replace(PascalString, "(?!^)([A-Z])", " $1");
}

Outputs:

Convert My Crazy Pascal Case Sentence To Friendly Case

If you're not using 2005, then you've gotta either parse it manually or reference the regex object using extended procedures. A good article can be found here:

http://www.codeproject.com/KB/mcpp/xpregex.aspx

Edit: A UDF can't affect the database, so you can't register the regex com object, so that casts that idea out. A stored procedure however, can - so that might be a route.

In order to do a case sensitive comparison, you're going to have to set the collation for the query to be case sensitive, and then use a replace I think... here's an article that might be helpful in pointing you off in the right direction:

http://www.mssqltips.com/tip.asp?tip=1032

BenAlabaster
I need to use a straight TSQL UDF. A CLR function is not an option for me.
Jose Basilio
Hmm, nasty, I'm not sure if it can be done using a UDF... because a UDF can't affect the database, so you can't register the regex library. You'd have to do it through a stored proc. I'm pretty sure you'll have to parse it manually.
BenAlabaster
+1  A: 
/*
 Try this.  It's a first hack - still has problem of adding extra space
 at start if first char is in upper case.
*/
create function udf_FriendlyName(@PascalName varchar(max))
returns varchar(max)
as
begin

    declare @char char(1)
    set @char = 'A'

    -- Loop through the letters A - Z, replace them with a space and the letter
    while ascii(@char) <= ascii('Z')
    begin
     set @PascalName = replace(@PascalName, @char collate Latin1_General_CS_AS, ' ' + @char) 
     set @char = char(ascii(@char) + 1)
    end

    return LTRIM(@PascalName) --remove extra space at the beginning

end
Rick
After adding SUBSTRING to remove the extra space, it works like a charm. Thank you!
Jose Basilio
Just a little suggestion: instead of the final return SUBSTRING(@PascalName,2,LEN(@PascalName)) --remove space at the beginningyou could have used return LTrim(@PascalName)Does not make a great difference in _this_ case, but in general it removes leading spaces (regardless of how many they are) but does not remove any other eventual character differente than space.
Turro
@Turro - Great tip. I didn't think of that at the time, but updated it now. Thank you!
Jose Basilio
+1  A: 

declare @arg varchar(20)
set @arg = 'DateOfBirthOnMonday'

declare @argLen int
set @argLen = len(@arg)

declare @output varchar(40)
set @output = ''

declare @i int
set @i = 1

declare @currentChar varchar(1)
declare @currentCharASCII int

while (1 = 1)
begin
set @currentChar = substring(@arg, @i, 1)
set @currentCharASCII = ascii(@currentChar)

if (@currentCharASCII >= 65 and @currentCharASCII <= 90)
set @output = @output + ' ' 

set @output = @output + @currentChar

set @i = @i+ 1

if (@i > @argLen) break
end

set @output = ltrim(rtrim(@output))
print @output

Change the value of @arg to something, you want to test with.

Also, you might need to change the @output declaration to accommodate string which has same length as the @arg + number of spaces it might need. I have doubled it in my example.

shahkalpesh
@Shahkalpesh - There's a syntax error on the line with: (@currentCharASCII >= 65 and @currentCharASCII @argLen)
Jose Basilio
I tested it too. It does not compile. -1
ichiban
SO ate <= sign. I formatted the post again. It should be OK now.
shahkalpesh
I retried it. That works +1
ichiban
+1  A: 

Not the most elegant solution, but it works:

declare @pascalCasedString nvarchar(max) = 'PascalCasedString'
declare @friendlyName nvarchar(max) = ''
declare @currentCode int;
declare @currentChar nvarchar;

while (LEN(@pascalCasedString) > 0)
    begin
     set @currentCode = UNICODE(@pascalCasedString)
     set @currentChar = NCHAR(@currentCode)

     if ((@currentCode >= 65) AND (@currentCode <= 90))
     begin
      set @friendlyName += SPACE(1)
     end
     set @friendlyName +=  @currentChar
     set @pascalCasedString = RIGHT(@pascalCasedString,LEN(@pascalCasedString) - 1)
    end

select @friendlyName
Colin Cochrane
This code does not compile in SQL Server. I assume this is either PLSQL or MySQL.
Jose Basilio
I think this code should work. Make it TSQL compliant and I'll give you an upvote.
ichiban
Upon further inspection, I found that syntax works under SQL Server 2008, but it fails for older versions. Nevertheless. +1
Jose Basilio