Function - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Function

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-02-28 : 17:17:49

[code]
I want a proper case function which should capitalize the first letter of each word for the string passed and also it should look into the table and ignore the words(i.e it should not capitalize and keep the word as is).

TableA IgnoreWordsTable
------ ----------------
ANDY DOL
sam CRIC
DOL
MANY-CRIC
CRIC
ASTRO-Quick

Output:

TableA
-------
Sam
DOL
Many-CRIC
CRIC
Astro-Quick

[/code]

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-02-28 : 17:22:09

You'll have to write such a function, SQL Server doesn't have anything like that built-in, especially the lookup part.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-02-28 : 17:59:28

Too bad Igor2004 isn't around any more.

He had a PROPER() function to capitalize the first letter of each word, but he never actually posted the code here.

He posted links to it many times, but they all seem to be down now.

CODO ERGO SUM

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-02-28 : 18:54:36

Thanks !!

The below function works fine but needs to modify for exceptions words..


CREATE FUNCTION udf_Case (@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

Thanks for you help in advance

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-03-01 : 07:59:21

[code]
Please help to customize the below function to ignore the exceptions words for Capitialize..

Posted - 02/28/2011 : 18:54:36 Show Profile Reply with Quote
Thanks !!

The below function works fine but needs to modify for exceptions words..

CREATE FUNCTION udf_Case (@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

[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources