I have found t-sql challenges question where they were asked to provide a solution of oracle INITCAP function in sql.
I was started to find the solution of INITCAP function & found lots of user defined function.
But here I have shared a solution where queries doesn't use any user defined function as well as any variable.
I was started to find the solution of INITCAP function & found lots of user defined function.
But here I have shared a solution where queries doesn't use any user defined function as well as any variable.
--First of all create one temp table
DECLARE @t TABLE(ID INT IDENTITY, Data VARCHAR(1000))
--insert the sample data
INSERT INTO @t
SELECT 'yogesh bhadauriya'
--use the Common table expression
;with CTE AS
(
--anchor query where we are finding the first character for replacing with uppercase
SELECT Cast(STUFF(LOWER(Data), 1, 1, UPPER(LEFT(Data, 1))) As varchar(max)) As Data,
PATINDEX('%[^a-zA-Z][a-z]%', Data COLLATE Latin1_General_Bin) As Position,ID,Data As OldData
From @t
Union All
--recursive query
SELECT Cast(STUFF(CTE.Data, Position, 2, UPPER(SUBSTRING(CTE.Data, CTE.Position, 2))) As Varchar(max)) as Data,
Cast(PATINDEX('%[^a-zA-Z][a-z]%', CTE.Data COLLATE Latin1_General_Bin) As Int) As Position,CTE.ID,T.Data As OldData
From @t As T
Inner Join CTE On CTE.ID=CTE.ID
where CTE.Data is not null
)
Select ID,OldData As Data,Data As Results
From CTE
where Data is not null
and Position=0
---output
--Id Data Results
--1 yogesh bhadauriya Yogesh Bhadauriya
