Home / Questions / 434253
Accepted
John Doe
Asked: 2022-03-10 15:50:00 +0800 CST 2022-03-10 15:50:00 +0800 CST

Convert first letter of each word to uppercase

  • 772

I have the following query in which I would like the field PUESTOto return its Capitalized value, I want to say the first letter of each word in uppercase and the rest in lowercase

I have the query as follows:

select 
        DEPTO,
        SUBSTRING(UPPER(PUESTO),1,1) + SUBSTRING(LOWER(PUESTO),2,50),
    from Personal

This query returns the value but with the first letter of the entire string capitalized, I would like it to be the first letter of each word.

sql

1 Answers

  1. Best Answer

    That type of case is called, in English, Proper Case or Title Case .

    You can write a function to perform the task, for example, in this StackOverflow answer in English, I found this function that does the dirty work:

    create function ProperCase(@Text as varchar(8000))
returns varchar(8000)
as
begin
  declare @Reset bit;
  declare @Ret varchar(8000);
  declare @i int;
  declare @c char(1);

  if @Text is null
    return null;

  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

    Once the function is created, use it in your select statement like so:

    select depto, dbo.ProperCase(puesto) as puesto
  from Personal;

    One suggestion I'd like to give you is that instead of calling this function every time, you update the information found in the table, for example:

    update personal
   set puesto = dbo.ProperCase(puesto);

    Of course, then, you have to do this every time you enter/update a new record, but it will make your database more efficient, since the number of times a record is done selectis usually greater than the number of times it is inserted/updated. these types of records.

    • 4