June 9, 2010 at 3:46 am
Hello all,
I have been testing another propercase function:
USE [MyDatabase]
GO
/****** Object: UserDefinedFunction [dbo].[ProperCase] Script Date: 06/08/2010 12:30:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[ProperCaseTest](@Text as nvarchar(4000))returns nvarchar(4000)as begin
declare @Reset bit;
declare @Ret nvarchar(4000);
declare @i int;
declare @C nchar(1);
select @Reset = 1, @i=1, @Ret = '';
while (@i <= len(@Text))
select @C= substring(@Text,@i,1),
@Ret = @Ret + case when @Reset=1 then UPPER(@c) else LOWER(@c) end,
@Reset = case when @C like '[a-zA-Z]' then 0 else 1 end,
@i = @i +1 return @Ret
end
However this function turns NULLs into blanks:
select dbo.propercasetest(NULL)
select dbo.propercasetest('random')
select dbo.propercasetest('NONSENCE')
select dbo.propercasetest('gOES')
select dbo.propercasetest('Here')
Is there anything I can do to prevent this? I am afraid that this is beyond my T-SQL knowledge, but I really would appreciate the help.
Paul
June 9, 2010 at 3:57 am
How about just doing this: -
USE [TestingDB]
GO
IF EXISTS (SELECT *
FROM sys.objects
WHERE object_id = Object_id(N'[dbo].[ProperCaseTest]')
AND TYPE IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
DROP FUNCTION [dbo].[ProperCaseTest]
GO
SET ansi_nulls ON
GO
SET quoted_identifier ON
GO
CREATE FUNCTION [dbo].[Propercasetest](@Text AS NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE @Reset BIT;
DECLARE @Ret NVARCHAR(4000);
DECLARE @i INT;
DECLARE @C NCHAR(1);
IF @Text IS NULL
BEGIN
SELECT @ret = NULL
RETURN @ret
END
SELECT @Reset = 1,
@i = 1,
@Ret = '';
WHILE ( @i <= Len(@Text) )
SELECT @C = Substring(@Text, @i, 1),
@Ret = @Ret + CASE
WHEN @Reset = 1 THEN Upper(@c)
ELSE Lower(@c)
END,
@Reset = CASE
WHEN @C LIKE '[a-zA-Z]' THEN 0
ELSE 1
END,
@i = @i + 1
RETURN @Ret
END
The new part is: -
IF @Text IS NULL
BEGIN
SELECT @ret = NULL
RETURN @ret
END
June 9, 2010 at 4:06 am
Thank you very much! That does the trick perfectly.
I see how it works too, which is good.
You have been a great help.
June 9, 2010 at 4:09 am
No problem. The reason it was returning blanks before is because the NULL was passed in, then the function was running: -
SELECT @Reset = 1,
@i = 1,
@Ret = '';
Which was setting your return (@Ret) to a value, instead of keeping a NULL.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply