how to convert selected text into Capital letters

Question

I have string where I need to convert them into Capital text in required string only

I have written query to do this using Charindex it is finding only one how to go for 2nd one I am only finding the 2nd word through index how to find other words also

select UPPER(LEFT(SUBSTRING('AHRQ utilization flag',CHARINDEX(' ','AHRQ utilization flag',0)+1,
len('AHRQ utilization flag')),1))

For example, Sample input:

    have a great day

My desired Output :

    Have A Great Day

how to do this one .......suggest me

possible duplicate of [SQL - Capitalize first letter of each word](http://stackoverflow.com/questions/11653491/sql-capitalize-first-letter-of-each-word) — Mikael Eriksson, Oct 24 '13 at 07:24
@MikaelEriksson I posted answer here. Would it be standard procedure to post on the other question or on this ? — t-clausen.dk, Oct 24 '13 at 17:12
@t-clausen.dk I don't know. Even if this Q is closed as dupe it will still be found in searches. One of [my most up voted answers](http://stackoverflow.com/questions/6661505/declare-variable-in-table-valued-function/6661756#6661756) is a closed Q. — Mikael Eriksson, Oct 24 '13 at 17:52

score 1 · Answer 1 · edited Oct 24 '13 at 07:02

You can write a function to convert desired string in camel case as below:

CREATE FUNCTION [dbo].[fn_CamelCase]
(@Str varchar(8000))
RETURNS varchar(8000) AS
BEGIN
  DECLARE @Result varchar(8000)
  SET @Str = LOWER(@Str) + ' '
  SET @Result = ''
  WHILE 1=1
  BEGIN
    IF PATINDEX('% %',@Str) = 0 BREAK
    SET @Result = @Result + UPPER(Left(@Str,1))+
    SubString  (@Str,2,CharIndex(' ',@Str)-1)
    SET @Str = SubString(@Str,
      CharIndex(' ',@Str)+1,Len(@Str))
  END
  SET @Result = Left(@Result,Len(@Result))
  RETURN @Result
END  
Go

and then call the same as:

select 'AHRQ ' + dbo.fn_CamelCase ('utilization flag')

SQL Fiddle Demo with MS SQL Server

+1, It works great, I have added demo link, @mohan111 go for this solution — Satpal, Oct 24 '13 at 07:03

vhadalgi · Answer 2 · 2013-10-24T08:16:11.667

0

CREATE FUNCTION udf_vijay (@InputString VARCHAR(1000) )
RETURNS VARCHAR(1000)
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

edited Oct 24 '13 at 08:16

answered Oct 24 '13 at 06:44

vhadalgi

7,027
6
38
67

vijay kumar after using this'll be Vijay Kumar ? – vhadalgi Oct 24 '13 at 06:45
i have this function but i am working on single string through queryselect UPPER(LEFT(SUBSTRING('AHRQ utilization flag',CHARINDEX(' ','AHRQ utilization flag',0)+1, len('AHRQ utilization flag')),1)) – mohan111 Oct 24 '13 at 06:46
'AHRQ utilization flag' in this how to make Capital 'U' and Capital 'F' – mohan111 Oct 24 '13 at 06:47
in sql server? version ? – vhadalgi Oct 24 '13 at 06:48
yeah i have used upper case and Charindex i am making it for first case 'U' how to make for Flag – mohan111 Oct 24 '13 at 06:53
@VijaykumarHadalgi, Your function is full of error. Try http://sqlfiddle.com/ and create a demo – Satpal Oct 24 '13 at 07:06
@Satpal http://sqlfiddle.com/#!6/703e0/5 the previous one is just a typo ..this one executes – vhadalgi Oct 24 '13 at 07:27

score 0 · Answer 3 · answered Oct 24 '13 at 06:53

One method could be:

Split the string into the first char UPPER and the rest like so:

UPPER(LEFT(string)) + RIGHT(string, LEN(string) - 1)

Then replace any space followed by a letter with the upper case of it such as:

REPLACE(string, ' a', ' A')

Combining the above, you can do this:

SELECT 
    UPPER(LEFT('have a great day')) + 
    REPLACE((((((((((((((((((((((((((
        RIGHT('have a great day', LEN('have a great day') - 1)
    , ' a', ' A'), ' b', ' B'), ' c', ' C')
    , ' d', ' D'), ' e', ' E'), ' f', ' F')
    , ' g', ' G'), ' h', ' H'), ' i', ' I')
    , ' j', ' J'), ' k', ' K'), ' l', ' L')
    , ' m', ' M'), ' n', ' N'), ' o', ' O')
    , ' p', ' P'), ' q', ' Q'), ' r', ' R')
    , ' s', ' S'), ' t', ' T'), ' u', ' U')
    , ' v', ' V'), ' w', ' W'), ' x', ' X')
    , ' y', ' Y'), ' z', ' Z')

Might need some tweaking since I haven't actually tested it

could you make it dynamic ? – vhadalgi Oct 24 '13 at 06:56 — vhadalgi, Oct 24 '13 at 06:56

t-clausen.dk · Answer 4 · 2013-10-24T09:43:32.110

create function f_CamelCase
(
  @txt nvarchar(max)
) returns nvarchar(max)
as 
begin 
set @txt = stuff(lower(@txt), 1,1,upper(left(@txt, 1)))

declare @index int = 1
while @index > 0
begin

  set @index = patindex('%[ ][abcdefghijklmnopqrstuvxyz]%', @txt  
            COLLATE SQL_Latin1_General_CP1_CS_AS)

  select @txt = stuff(@txt, @index+ 1, 1, upper(substring(@txt, @index + 1, 1)))
  where @index > 0

end

return @txt
end

Test:

select dbo.f_CamelCase('aa b A b c')

Result:

Aa B A B C

how to convert selected text into Capital letters

4 Answers4