HI,
i got a problem while using StrConv function in sql server.
My requirement is:-
suppose in a name field i have "jhon smith" Now i want to run a sql
which will give me the result like "Jhon Smith"--which means upper
case first.

Now i am running a query like below which is giving me "Jhon smith"
but i want "Jhon Smith".

SELECT [CustomerID] ,upper(left(firstname,1)) + lower(right(firstname,
len(firstname)-1)) AS [FirstName_] from my_table;


But the above query is not giving me the right result. Please
suggest...


Thanks
Hoque

On 28 Jul 2003 23:18:53 -0700, sohelcsc@yahoo.com (Leader) wrote:

>HI,
>i got a problem while using StrConv function in sql server.
>My requirement is:-
>suppose in a name field i have "jhon smith" Now i want to run a sql
>which will give me the result like "Jhon Smith"--which means upper
>case first.
>
>Now i am running a query like below which is giving me "Jhon smith"
>but i want "Jhon Smith".
>
>SELECT [CustomerID] ,upper(left(firstname,1)) + lower(right(firstname,
>len(firstname)-1)) AS [FirstName_] from my_table;
>
>
>But the above query is not giving me the right result. Please
>suggest...
>

Not sure if this is the most efficient (this sort of stuff is best
handled programmically at the client end), but this is what I came up
with. I am assuming you have one name field with both the first and
the last in it with a single space between them (even though your
field name is 'firstname').

select case patindex('% %',rtrim(name))
when 0 then
upper(left(name,1)) + lower(substring(name,2,len(name)-1))
else
upper(left(name,1)) + lower(substring(name,2,
patindex('% %',name)-1)) +
upper(substring(name,patindex('% %',name)+1, 1)) +
lower(substring(name,patindex('% %',name)+2,
len(name)-patindex('% %',name)-1))
end as name
from table1

p.s. This won't work for names like T. I. Jones (you will get T. I.
jones), but you shouldn't be putting the name in one field anyway
<grin>. If you control the database, I suggest you use this statement
to split the names into two fields.

Dan Bush
me@R3M0V3.danbush.com

sohelcsc@yahoo.com (Leader) wrote in message news:<b1a93c73.0307282218.74758994@posting.google. com>...
> HI,
> i got a problem while using StrConv function in sql server.
> My requirement is:-
> suppose in a name field i have "jhon smith" Now i want to run a sql
> which will give me the result like "Jhon Smith"--which means upper
> case first.
>
> Now i am running a query like below which is giving me "Jhon smith"
> but i want "Jhon Smith".
>
> SELECT [CustomerID] ,upper(left(firstname,1)) + lower(right(firstname,
> len(firstname)-1)) AS [FirstName_] from my_table;
>
>
> But the above query is not giving me the right result. Please
> suggest...
>
>
> Thanks
> Hoque

You can find one possible solution here:

http://vyaskn.tripod.com/code.htm#propercase

Simon