Home 2017 November SQL Puzzle: SQL Advance Query – Create a function to get the string in InitCap Format

SQL Puzzle: SQL Advance Query – Create a function to get the string in InitCap Format

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Check the below sample function execution and expected output to get the given string data in InitCap format.

Sample function execution:

Expected Output:

Solution:

Please try the different solution for this puzzle and share it via comment...

Anvesh Patel
Greenplum: Script to find information for Long running Queries with occupied resourceGreenplum: How to Start and Stop Database?
Comments: 3
  1. Ashutosh Srivastava
    January 5, 2018 at 6:08 am

    HI Avnesh, Thanks for your explanation. I have one situation i.e. There is one column where 100 records are available. Value is like accountnumber, subaccountnumber etc. I need an output like: Account_Number, Sub_Account_Number.

    Could you please help me regarding this.

    Regards,
    Ashutosh Srivastava

    ReplyCancel
  2. sqlguru
    July 14, 2019 at 6:43 am

    create function dbo.fn_GetInitCap(@inputStr varchar(255)) returns varchar(255) as
    begin
    declare @newstr varchar(255);

    set @inputStr = replace(@inputStr, ‘ ‘,’~’);
    with cte as (
    select @inputStr inpt, cast(” as varchar(255)) items
    union all
    select cast(stuff(inpt,1,iif(PATINDEX(‘%[^a-zA-Z]%’,inpt) > 0, PATINDEX(‘%[^a-zA-Z]%’,inpt),len(inpt) ),”) as varchar(255)), substring(inpt, 1, iif(PATINDEX(‘%[^a-zA-Z]%’,inpt) > 0, PATINDEX(‘%[^a-zA-Z]%’,inpt),len(inpt) )) from cte where inpt ”
    ) –select * from cte;
    , cte2 as(
    select items, row_number() over (order by (select null)) rn
    from cte
    where items ”
    ) –select * from cte2;
    , cte3 as(
    select items, rn, cast(” as varchar(1)) ltr, 0 idx from cte2
    union all
    select stuff(items,1,1,”), rn, substring(items,1,1), idx + 1 from cte3 where items ”
    )
    select @newstr = replace(
    (select ltr2+” from (
    select cte3.*, iif(idx=1, upper(ltr),lower(ltr)) ltr2 from cte3
    where ltr ”
    –order by rn, idx
    ) t order by rn, idx for xml path(”)),’~’,’ ‘);

    return @newstr;

    end
    go

    select dbo.fn_GetInitCap(‘Hi I Am Anvesh Patel Owner Of Dbrnd.Com’);

    ReplyCancel
  3. Samrat
    October 24, 2019 at 12:37 pm

    CREATE FUNCTION InitCap(@Value VARCHAR(2000))
    RETURNS VARCHAR(2000)
    AS
    BEGIN
    DECLARE @R_VALUE VARCHAR(2000)=”

    SELECT @R_VALUE+=STRING_AGG(UPPER(SUBSTRING(VALUE,1,1))+LOWER(SUBSTRING(VALUE,2,LEN(VALUE))),’ ‘)
    FROM STRING_SPLIT(@Value,’ ‘)

    RETURN @R_VALUE
    END

    ReplyCancel

Leave a Reply

CAPTCHA

*

Anvesh Patel
Anvesh Patel

Database Engineer

November 27, 2017 3 Comments SQL Puzzle, , , , , , , , , , ,