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) ) 

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)
    SET @Char     = SUBSTRING(@InputString, @Index, 1)
    SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
                         ELSE SUBSTRING(@InputString, @Index - 1, 1)

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

    SET @Index = @Index + 1

RETURN @OutputString


Can anyone help me change the function for this circumstance?


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.