Is there a function in SQL like "initcap" in oracle

Last Post 25 Mar 2003 07:05 AM by niemingxin. 5 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
niemingxin
New Member
New Member

--
24 Mar 2003 07:23 AM
The function can convert the fisrt character of a string into uppercase
niemingxin
New Member
New Member

--
24 Mar 2003 12:40 PM
So how can I do that?
bigelectricmac
New Member
New Member

--
24 Mar 2003 01:41 PM
Remember that the ASCII value of the upper case letter is 32 less than the lower case letter ...

DECLARE @in_string VARCHAR(6)

SELECT @in_string = 'abcdef'

SELECT @in_string = CHAR(ASCII(SUBSTRING(@in_string,1,1)) -32) + SUBSTRING(@in_string,2 ,LEN(@in_string))

SELECT @in_string

Hope this helps

Paul



bigelectricmac
New Member
New Member

--
24 Mar 2003 03:05 PM
Of course ... you can always use UPPER instead of manually working it out (forgot about that one!!)

SELECT @in_string = UPPER(SUBSTRING(@in_string,1,1)) + SUBSTRING(@in_string,2 ,LEN(@in_string))
niemingxin
New Member
New Member

--
25 Mar 2003 07:05 AM
Thanks. Sorry that I may not clearly state my question. what I want is to uppercase the fisrt character of each word of a string. ex.

"how arE YOu DoIng?" -> "How Are You Doing?"
bigelectricmac
New Member
New Member

--
25 Mar 2003 04:00 PM
You'll need to create a UDF ... try this ... (you'll have to re-format it for indentation and clarity - the editor on this site strips out leading spaces!)

CREATE FUNCTION InitCap (@in_string VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN

DECLARE @str_length INTEGER,
@format_string VARCHAR(100),
@char_count INTEGER,
@previous_is_space TINYINT

SELECT @str_length = LEN(@in_string),
@format_string = '',
@char_count = 0,
@previous_is_space = 1 -- true

--Need to step thru the string
WHILE @char_count < @str_length
BEGIN

SELECT @char_count = @char_count + 1

IF @previous_is_space = 1 -- Always true for first character
BEGIN
IF SUBSTRING(@in_string, @char_count, 1) = ' '
-- Allow for multiple space
BEGIN
SELECT @previous_is_space = 1 -- true
END
ELSE
BEGIN
SELECT @previous_is_space = 0 -- false
END
SELECT @format_string = @format_string + UPPER(SUBSTRING(@in_string, @char_count, 1))
END
ELSE
BEGIN
IF SUBSTRING(@in_string, @char_count, 1) = ' '
BEGIN
SELECT @previous_is_space = 1 -- True
END
SELECT @format_string = @format_string + LOWER(SUBSTRING(@in_string, @char_count, 1))
END
END

RETURN(@format_string)
END

You are not authorized to post a reply.

Acceptable Use Policy