Posted by d-train in SWF Studio V3 on Apr 14 2016, 03:00 am

Hi All

I have a SQL function to change descriptions to Title case (first letter is a capital), but I circumstances where words need to remain as capitals e.g. brand names like IBM I want to remain as IBM and not Ibm.

the SQL function is...


CREATE FUNCTION [dbo].[InitCap] ( @InputString varchar(4000) ) 
RETURNS VARCHAR(4000)
AS
BEGIN

DECLARE @Index          INT
DECLARE @Char           CHAR(1)
DECLARE @PrevChar       CHAR(1)
DECLARE @OutputString   VARCHAR(255)

SET @OutputString = LOWER(@InputString)
SET @Index = 1

WHILE @Index <= LEN(@InputString)
BEGIN
    SET @Char     = SUBSTRING(@InputString, @Index, 1)
    SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
                         ELSE SUBSTRING(@InputString, @Index - 1, 1)
                    END

    IF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')
    BEGIN
        IF @PrevChar != '''' OR UPPER(@Char) != 'S'
            SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char))
    END

    SET @Index = @Index + 1
END

RETURN @OutputString

END
GO


Can anyone help me change the function for this circumstance?

Thx


Posted by northcode in SWF Studio V3 on Apr 14 2016, 04:52 am

I don't have a SQL Server running but I would probably try using CHARINDEX to look for spaces (or PATINDEX if your word separators are more complex) and pull out words. Compare the UPPER version of each word with the untouched version of the word. If they are the same, leave the word alone. If they are different, convert the word to LOWER and then uppercase the first letter of the word. Move on to the next word until there are no more.