April 19, 2004 at 2:03 pm
We just completed an import of customers from another system and have noticed that the text is all in capital letters. Is there a function to convert to proper case?
Thanks
April 20, 2004 at 3:26 am
Apparently not. You can do pretty much everything but proper case in SQL Server.
Does it need to be in proper case in the database or can you convert to proper in your front-end app (if any)?
If using SQL 2000 you could always write a UDF to do it, but depending on how it is written it could be slow and nasty (e.g. move through string one character at a time in a loop, capitalise first letter, lower case each next concatenating to new string, when space is encountered capitalise next and so on).
April 21, 2004 at 1:13 am
Check out this link – it may help you out.
http://www.sqlservercentral.com/columnists/sjones/tamethosestringspart7.asp
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 21, 2004 at 2:55 am
I am using the function below to convert to Proper Case.
--**********************************************************
CREATE FUNCTION ProperCase
(@STRING AS VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @tstring VARCHAR(8000)
DECLARE @trimmed VARCHAR(8000)
DECLARE @index INT
SELECT @trimmed = RTRIM(LTRIM(@STRING))
If @trimmed is NULL
BEGIN
SELECT @tstring = NULL
END
ELSE
BEGIN
SELECT @index = CHARINDEX(' ',@trimmed)
IF @index = 0
BEGIN
SELECT @tstring = UPPER(Left(RTRIM(LTRIM(@trimmed)),1)) + LOWER(RIGHT(RTRIM(LTRIM(@trimmed)),LEN(RTRIM(LTRIM(@trimmed)))-1))
END
ELSE
BEGIN
SELECT @tstring = dbo.ProperCase(RTRIM(LTRIM(Left(@trimmed,@index - 1)))) + ' ' + dbo.ProperCase(RTRIM(LTRIM(Right(@trimmed,LEN(@trimmed) - @index))))
END
END
RETURN @tstring
END
April 21, 2004 at 4:48 am
Here is another way to do it. It mimics the InitCap function in Oracle
CREATE function dbo.fn_InitCap
(
@inString varchar(4000)
)
returns varchar(4000)
as
BEGIN
/******************************************************************
INITCAP returns char, with the first letter of each word in
uppercase, all other letters in lowercase.
*******************************************************************/
DECLARE @i int,
@a int,
@result varchar(255)
IF @inString is Null
Set @result = Null
ELSE
BEGIN
SET @result = LOWER(@inString)
SET @i = 2
SET @result = STUFF(@result,1,1,UPPER(SUBSTRING(@inString,1,1)))
WHILE @i <= LEN(@inString)
BEGIN
SET @a = ascii(SUBSTRING(@inString,@i,1))
IF @a < ascii('A') or (@a > ascii('Z') and @a < ascii('a')) or @a > ascii('z')
IF @i < LEN(@inString)
SET @result=STUFF(@result,@i+1,1,UPPER(SUBSTRING(@inString,@i+1,1)))
SET @i=@i+1
END
END
RETURN @result
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
September 13, 2011 at 7:29 am
Scott Williams (4/21/2004)
Here is another way to do it. It mimics the InitCap function in Oracle
CREATE function dbo.fn_InitCap
(
Tnx all
Tnx Williams, your one is the most elegant in my opinion
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply