Sql-server – Pass All Table Values Into Function

sql serversql-server-2008-r2t-sql

I found a function that will properly capitalize text strings. I have a table where my fields need this function run on. Is it possible to run the function on my table?

Create Table @Garbage
(
  fullname varchar(500)
  ,saddy varchar(500)
)

Insert Into @Garbage (fullname, saddy) VALUES
('BlAmE iT oN tHe RaIn', 'APPALACHACOLA FUNVILLE FOOTHILLS')
,('RED DIRT ROAD', 'purple visors block THE sun')
,('blueBERRIES taste YUM', 'bLaCk CaTs')


ALTER FUNCTION [dbo].[fnConvert_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

Best Answer

Verify your intended change using SELECT

SELECT
    fullname = dbo.fnConvert_TitleCase(fullname),
    saddy = dbo.fnConvert_TitleCase(saddy)
from
 @Garbage

and then, if you're satisfied, run the update below

update @Garbage set 
    fullname = dbo.fnConvert_TitleCase(fullname),
    saddy = dbo.fnConvert_TitleCase(saddy)

That being said, scalar functions don't really perform very well - a better solution would be to create a table value function and then you could use CROSS APPLY