tranfer "paul" into "Paul"

[payoff12]

In the database I have a name field which contains name of members. Unfortunately some names are just in lower cast, some in just in upper cast. How can i use sql to pull the data from the database where the first character is uppercast and the other characters are lower cast.

Some examples:
Paul should become Paul
PAUL should become Paul
paul should become Paul

 

[grega]

You mean you want to update the names? Or select them? The syntax depends on yout DB. In Sybase you could do

select upper(substring(fieldname,1,1)) + lower(substring(fieldname,2,char_length(fieldname)))
from tablename

To update, you could do

update tablename
set fieldname = upper(substring(fieldname,1,1)) + lower(substring(fieldname,2,char_length(fieldname)))
from tablename

Greg.

 

[NateUNI]

You could also use the INITCAP funtion if you database supports it

 

[bdreed35]

Here is a SQL Server function that I have used to change the first letter in every word in a sentence to upper case. You could modify it to look for the first word after certain punctuation marks if you had multiple sentences.

CREATE FUNCTION dbo.f_Proper_Case ( @v_IN_String  varchar(8000))
RETURNS VARCHAR(8000) AS  
BEGIN 
DECLARE
 @v_OUT_String     varchar(8000)
 
set @v_IN_String = Lower(@v_IN_String) + ' '
set @v_OUT_String = ''
 
while ( charindex(' ',@v_IN_String,1) > 0)
 begin
      set @v_OUT_String = @v_OUT_String + Upper(left(@v_IN_String,1)) + substring(@v_IN_String,2,charindex(' ',@v_IN_String,1) - 1)
      set @v_IN_String = right(@v_IN_String, len(@v_IN_String) - charindex(' ',@v_IN_String,1) + 1)
 end
 
RETURN( @v_OUT_String )
END

~Brian