0

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
2

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

  • Thank you for the note on performance. I will look into a table value function! – SmallFries BigGuys Oct 12 '16 at 15:36
0

absolutely you can run this function on your table although you will have to apply this function to each column. However,scaler functions are bad for performance when executing on a large dataset so it may not be useful for you.

SELECT [dbo].[fnConvert_TitleCase]([fullname]),
      [dbo].[fnConvert_TitleCase]([saddy])
FROM [Garbage];

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.