How to I get proper case for the first name field?

With the value: Mary Ellen, or mary ellen, or Mary ellen, or mary Ellen,

strFirstName = RTrim(DTSSource("First_Name")

strFirstName = UCase(Left(strFirstName,1)) & LCase(StrFirstName)

I get Mary ellen

TIA

JeffP...

There is no StrConv() function in VBScript so we have to make our own

Have a look here

http://www.asp101.com/samples/viewas...le=pcase%2Easp


You can also do it in TSQL

Q. How can I convert a string to "proper" case in SQL Server? i.e. Turn
"andrew" into "Andrew"

A. SQL Server has no built-in function for this and neither does it support
user-defined functions. Therefore your two choices are :-

1. Write an extended stored-procedure
2. Write a TSQL stored-procedure

An XP is faster and lets you have the full range of C programming tools and
techniques, however it is possible to implement a simple example in TSQL.

If you only have a surname to update in a single field, then it is possible
to
do it in a single update statement. Example below.

UPDATE <tbl>
SET surname = substring(surname,1,1) +
lower(substring(surname,2,(datalength(surname)-1)))

The below is an example of an sp. It only handles simple cases, and won't
do
stuff like D'Arcy properly - if you want full function stuff you are
recommended to write your own generic C routine and then call it from an XP.

create procedure sp_proper
@in varchar(255) output
as
BEGIN
declare @in_pos tinyint,
@inter varchar(255),
@inter_pos tinyint

select @in_pos = 0,
@in = lower(@in)
select @inter = @in
select @inter_pos = patindex('%[0-9A-Za-z]%', @inter)
while @inter_pos > 0
begin
select @in_pos = @in_pos + @inter_pos
select @in = stuff(@in, @in_pos, 1, upper(substring(@in, @in_pos, 1))),
@inter = substring(@inter, @inter_pos + 1, datalength(@inter) -
@inter_pos)
select @inter_pos = patindex('%[^0-9A-Za-z]%', @inter)
if @inter_pos > 0
begin
select @in_pos = @in_pos + @inter_pos
select @inter = substring(@inter, @inter_pos + 1, datalength(@inter) -
@inter_pos)
select @inter_pos = patindex('%[0-9A-Za-z]%', @inter)
end
end
END
go

===

v1.02 2000.01.27
Applies to SQL Server versions : All
FAQ Categories : Application Design and Programming
Related FAQ articles : n/a
Related Microsoft Kb articles : n/a
Other related information : n/a
Authors : Neil Pike



--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"JDP@Work" <JPGMTNoSpam (AT) sbcglobal (DOT) net> wrote

Be sure to have an exception list for cases like MaryAnn.

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

First off the script worked, I modified the header to give credit and pruned the
other comments....

I've been working to hard and was using complex Mid's and Instr's without
keeping my place w/an Integer.

I should also include logic to handle McCarthy as well.....

If Not ascii(mid(strInput,1,1) between X and Xn Then...

Is there a VBS equivalent to TSQL ascii?

I'll have to lookup my Ascii to get the X to Xn integer range.

TIA

JeffP...

"Ilya Margolin" <ilya (AT) unapen (DOT) com> wrote