Title Case Function 1

[ronwmon]

Hello All,

I need a little help. I have a function that will title case a field. My routine to call the function is:

update TableNname
set FieldName = dbo.fn_title_case (FieldName)
where FieldName is not null and FieldName not like ''

I need functions that will do the following:

Make text uppercase where the string is X.X. That is a letter followed by a period, another letter then another period. This will be helpful where a persons initials are being used like R.E. Wies or it is a post office bix address like P.O. Box XXX.

I also need to be able to look for a & and make uppercase the letters that appear on either side such as A&A.

I also need to do the same with a hyphen so that a name like Tri-State will have uppercase T & S.

My current uppercase function looks for a space and makes anything after a space uppercase.

I need seperate functions for each of these, they don't need to be incororated into one function. I need to specify the table and the field as I have several in this conversion.

Your help is greatly appreciated.

Ron--

 

[gmmastros]

What have you tried so far?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ronwmon]

The code for my current function is below. I don't fully understand the code so I'm not sure exactly to change it to accomplish what I need. I was hoping somebody on Tec-Tips would be able to provide me a little code.

Thanks,

Ron--

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER FUNCTION dbo.fn_title_case
(
@str AS varchar(100)
)
RETURNS varchar(100)
AS
BEGIN

DECLARE
@ret_str AS varchar(100),
@pos AS int,
@len AS int

SELECT
@ret_str = ' ' + LOWER(@str),
@pos = 1,
@len = LEN(@str) + 1

WHILE @pos > 0 AND @pos < @len
BEGIN
SET @ret_str = STUFF(@ret_str,
@pos + 1,
1,
UPPER(SUBSTRING(@ret_str,@pos + 1, 1)))
SET @pos = CHARINDEX(' ', @ret_str, @pos + 1)
END

RETURN RIGHT(@ret_str, @len - 1)

END


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

[gmmastros]

Would you rather be given some code, or would you rather understand the code you've already got?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ronwmon]

Understanding the code would be most helpful.

Ron--

 

[gmmastros]

First, let's examine the functions that are used within this User Defined Function.

Upper
Lower
Len
Right
SubString
CharIndex
Stuff

Books on line will give you more information than I can.

Upper converts strings to all UPPER CASE.
Lower converts strings to all lower case.
Len returns the length of a string.
Right returns part of string. Ex: Right('George', 3) = rge
SubString returns part of a string. Ex: SubString('George', 3, 2) = or

CharIndex returns the position of 1 string within another. There is a rarely used 3rd parameter that specifies the starting point.

Ex:
Select CharIndex('e', 'george') -- Returns 2
Select CharIndex('e', 'george', 3) -- Returns 6

The second ex looks for the letter e in the string george, but starts at the 3rd position. The first e is at 2, but starting with the 3rd character, the next e is the 6th character.

Stuff:

The intended purpose of this function is to add strings to the middle of existing strings. This function has 4 parameters. The first represents the string to work on. The second parameter indicates where (in the original string) to start 'stuffing' characters. The 3 parameter indicates the number of characters to remove. The 4th parameter indicates the string to 'stuff' in to the other.

Ex:

Select Stuff('George', 3, 1, 'A')

Using the string 'George', at the 3rd character position, remove 1 character and add the string 'A'. This results in 'GeArge'.

So far so good? Does this make sense?

Essentially, this function

1. Converts the entire string to lower case.
2. Loops through the string, looking for spaces.
3. When it finds a space, it replaces the next character (@pos + 1) with the UPPER CASE representation of that character ( Upper(SubString) ).

Does this make sense now. If not, let me know and I will explain more.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom