MSSQL equivalent of Oracle Initcap 1

[jmikow]

Does anyone have a script, or stored procedure that will produce the same results as the InitCap function in Oracle? I know there is Upper() and Lower(), but does this exist for MSSQL. We're using MSSQL 2000.

Thanks,

Josh

 

[pgtek]

hi
yes it does
see
UPPER() UPPER (char_expr) Converts lowercase character data to uppercase.
cheers

pgtek

 

[jmikow]

The InitCap in Oracle converts the 1st character of each word to Capital and the rest to lowercase. Does this functionality exist in MSSQL 2000?

 

[pgtek]

hi
check

------------------
INITCAP Function in SQL Server

CREATE function INITCAP (@inString varchar(4000) )
/* INITCAP returns char, with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric */
returns varchar(4000)
as
BEGIN
DECLARE @i int, @c char(1),@result varchar(255)
SET @result=LOWER(@inString)
SET @i=2
SET @result=STUFF(@result,1,1,UPPER(SUBSTRING(@inString,1,1)))
WHILE @i<=LEN(@inString)
BEGIN
SET @c=SUBSTRING(@inString,@i,1)
IF (@c=' ') OR (@c=';') OR (@c=':') OR (@c='!') OR (@c='?') OR (@c=',')OR (@c='.')OR (@c='_')
IF @i<LEN(@inString)
BEGIN
SET @i=@i+1
SET @result=STUFF(@result,@i,1,UPPER(SUBSTRING(@inString,@i,1)))
END
SET @i=@i+1
END
RETURN @result
END
---------------------------

cheers

pgtek

 

[jmikow]

This appears to be working great. Thanks a lot for the help!

 

[pgtek]

your welcome

pgtek

 

[TheBugSlayer]

Try this in QA:

begin transaction
declare @MyVar varchar(200)
Select @MyVar = 'hello WOrld!'

declare @Length int
select @Length = len(@MyVar)

declare @Res varchar(200)

select @Res = Upper(substring(@MyVar,1,1))
select @Res = @Res + Lower(substring(@MyVar, 2, @Length - 1))

--select @MyVar
p rint @Res

commit transaction

You should get 'Hello world!'

 

[SQLBI]

Excellent pgtek, exactly what i was looking for.

Thanks and have a well deserved star.



Leigh Moore
Business Systems Manager
Vets4Pets Veterinary Group

http://www.vets4pets.eu.com

 

[ESquared]

You inspired me to write my own function...

CREATE FUNCTION InitCap(@TheString varchar(8000))
RETURNS varchar(8000)
AS
BEGIN
   DECLARE @pos int
   SET @TheString = Lower(@TheString)
   SET @pos = 1
   WHILE @pos IS NOT NULL BEGIN
      SET @TheString = Stuff(@TheString,@pos,1,Upper(SubString(@TheString,@pos,1)))
      SET @pos = @pos + NullIf(PatIndex('%[^a-z0-9][a-z]%',SubString(@TheString,@pos+1,8000)),0) + 1
   END
   RETURN @TheString
END

I like it a lot.

I wonder if it's more or less expensive than pgtek's?

-------------------------------------
A sacrifice is harder when no one knows you've made it.