First charactor upper case using delimiter

 

First charactor upper case using delimiter

Declare @RowData nvarchar(2000)

Declare @SplitOn nvarchar(5)

Declare @Cnt int

Declare @cnt1 int

Declare @init int

Declare @datavalue nvarchar(200)

Declare @len int

Declare @datavalue1 nvarchar(200)

select @RowData='split. a string based on delimiter',@SplitOn=' '

select @Cnt = 1

select @datavalue=''

select @init=1

select @datavalue1=''

Declare @RtnValue table

(

Id

int identity(1,1),

Data

nvarchar(100)

)

select @RowData=REPLACE(@RowData,'.','')

/*Break the string using delimiter and then insert that in to temp table*/

While (Charindex(@SplitOn,@RowData)>0)

Begin

Insert Into @RtnValue (data)

Select

Data

= ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))

End

Insert Into @RtnValue (data)

Select Data = ltrim(rtrim(@RowData))

select @cnt1=count(*) from @RtnValue

/*update first char upper case remaining lower case*/

while @init<=@cnt1

begin

select @datavalue=DATA from @RtnValue where Id=@init

select @len=len(@datavalue)

update @RtnValue set DATA=upper(left(@datavalue,1))+SUBSTRING(@datavalue,2,@len-1) where Id=@init

select @init = @init + 1

end

select @init=1

/*Concate that char to original position.*/

while @init<=@cnt1

begin

select @datavalue1=@datavalue1+' '+DATA from @RtnValue where Id=@init

select @init=@init+1

end

select @datavalue1 as 'Capital'

 

--Output

 

Capital

--------------------------------------

Split A String Based On Delimiter