Value conversion into lowercase

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Value conversion into lowercase

Author

Topic

aoriju
Posting Yak Master

156 Posts

Posted - 2011-09-27 : 03:25:39

Hi all,

I have a value like 'COHIN'

i want to get the result as 'Cohin'

can u pls help me

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-27 : 03:41:48

try


LEFT(col, 1) + lower(substring(col, 2, len(col) - 1))

KH
[spoiler]Time is always against us[/spoiler]

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-27 : 03:59:20

DECLARE @str VARCHAR(100)
SET @str = 'COHIN CHENNAI KERELA'

--If you are sure always the input will be in ALL caps
SELECT LEFT(@str,1) + LOWER(RIGHT(@str,LEN(@str)-1))

--If in doubt :)
SELECT UPPER(LEFT(@str,1)) + LOWER(RIGHT(@str,LEN(@str)-1))

Best Regards
Vadivel

http://vadivel.blogspot.com

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-09-27 : 04:21:48

SQL SERVER – UDF – Function to Convert Text String to Title Case – Proper Case

Following function will convert any string to Title Case. I have this function for long time. I do not remember that if I wrote it myself or I modified from original source.

Run Following T-SQL statement in query analyzer:

SELECT dbo.udf_TitleCase('This function will convert this string to title case!')

The output will be displayed in Results pan as follows:

This Function Will Convert This String To Title Case!

T-SQL code of the function is:

CREATE FUNCTION udf_TitleCase (@InputString VARCHAR(4000) )
RETURNS VARCHAR(4000)
AS
BEGIN
DECLARE @Index INT
DECLARE @Char CHAR(1)
DECLARE @OutputString VARCHAR(255)
SET @OutputString = LOWER(@InputString)
SET @Index = 2
SET @OutputString =
STUFF(@OutputString, 1, 1,UPPER(SUBSTRING(@InputString,1,1)))
WHILE @Index <= LEN(@InputString)
BEGIN
SET @Char = SUBSTRING(@InputString, @Index, 1)
IF @Char IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&','''','(')
IF @Index + 1 <= LEN(@InputString)
BEGIN
IF @Char != ''''
OR
UPPER(SUBSTRING(@InputString, @Index + 1, 1)) != 'S'
SET @OutputString =
STUFF(@OutputString, @Index + 1, 1,UPPER(SUBSTRING(@InputString, @Index + 1, 1)))
END
SET @Index = @Index + 1
END
RETURN ISNULL(@OutputString,'')
END

For more detail read : http://blog.sqlauthority.com/2007/02/01/sql-server-udf-function-to-convert-text-string-to-title-case-proper-case/

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources