Monday, April 4, 2011

Convert String to "Proper" Case

The following T-SQL user defined function converts a string value to "Proper" case:

SET QUOTED_IDENTIFIER ON
GO

SET
ANSI_NULLS ON
GO

PRINT
'drop function [dbo].[udfProper]'
GO

if
exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[udfProper]')
and xtype in (N'FN', N'IF', N'TF'))

drop function [dbo].[udfProper]
GO

PRINT
'CREATE function dbo.udfProper'
GO

/******************************************************************************
NAME: udfProper

PURPOSE: Converting a String to "Proper" Case

REVISIONS:

$Log$

Ver Date Author Description
--------- ---------- --------------- ------------------------------------

******************************************************************************/

CREATE FUNCTION dbo.udfProper(@in varchar(255))
RETURNS varchar(255)
as

BEGIN

DECLARE @in_pos tinyint
,@inter varchar(255)
,@inter_pos tinyint

select @in_pos = 0, @in = lower(@in)
select @inter = @in
select @inter_pos = patindex('%[0-9A-Za-z]%', @inter)
while @inter_pos > 0

begin

select @in_pos = @in_pos + @inter_pos
select @in = stuff(@in, @in_pos, 1, upper(substring(@in, @in_pos, 1))),
@inter = substring(@inter, @inter_pos + 1, datalength(@inter) - @inter_pos)
select @inter_pos = patindex('%[^0-9A-Za-z]%', @inter)

if @inter_pos > 0
begin
select @in_pos = @in_pos + @inter_pos
select @inter = substring(@inter, @inter_pos + 1, datalength(@inter) - @inter_pos)
select @inter_pos = patindex('%[0-9A-Za-z]%', @inter)
end

end

RETURN(@in)
END

GO

SET
QUOTED_IDENTIFIER OFF
GO

SET
ANSI_NULLS ON
GO


If you find this script useful please donate generously.

No comments:

Post a Comment