Home 2017 September SQL Puzzle: Write a Function to count the total number of word

SQL Puzzle: Write a Function to count the total number of word

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

Create a user defined function to count the total number of a word from the given string data.

Sample function execution:

Expected Output:

User defined function:

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

Anvesh Patel
Greenplum: Important Queries to Configure the Resource Queue (Workload Management)SQL Server: BULK INSERT for insert data from Flat file (CSV) to Table
Comments: 4
  1. Dinesh
    September 12, 2017 at 7:33 am

    –Method-1

    Declare @XML As XML;
    Set @XML=”+Replace(‘I am Database Engineer and I am the owner of dbrnd.com’,’ ‘,”)+”
    Select Count(r.value(‘.’,’NVarChar(Max)’)) As TotalWordCount
    From @XML.nodes(‘t’) As Records(r)

    GO

    –Method-2

    Declare @SQL NvarChar(Max)=’I am Database Engineer and I am the owner of dbrnd.com’
    Declare @Len As Int
    Declare @Cnt Int
    Set @Cnt=0
    While Len(@SQL)>0
    Begin

    Set @Len=(Case CharIndex(‘ ‘,@SQL) When 0 Then Len(@SQL) Else CharIndex(‘ ‘,@SQL)-1 End)

    Set @Cnt=@Cnt+1
    Set @SQL=(Case Len(@SQL)-@Len When 0 Then NULL Else Right(@SQL,Len(@SQL)-@Len-1) End)

    End
    Select @Cnt As TotalWordCount
    GO

    ReplyCancel
  2. harini
    September 18, 2017 at 7:31 am

    declare @q varchar(max) = ‘I am Database Engineer and I am the owner of dbrnd.com’;
    select len(@q) – len(replace(@q,’ ‘,”)) + 1

    ReplyCancel
  3. VISHNUAUTO
    September 22, 2017 at 6:14 am

    ALTER FUNCTION [dbo].[fn_GetWordCount] (@InputStringData VARCHAR(100))
    RETURNS INT
    AS
    BEGIN

    DECLARE @i INT
    DECLARE @Char CHAR(1)
    DECLARE @PrevChar CHAR(1)
    DECLARE @Counter INT

    declare @input_xml xml

    select @input_xml = ” + replace(@InputStringData,’ ‘,” ) + ”

    SELECT @Counter=count(x.value(‘.’,’nvarchar’))
    FROM @input_xml.nodes(‘//*:root/*:x’) y(x)
    where y.x.value(‘.’,’nvarchar(100)’) ”

    RETURN @Counter
    END
    GO

    ReplyCancel

Leave a Reply to Dinesh Cancel reply

CAPTCHA

*

Anvesh Patel
Anvesh Patel

Database Engineer

September 11, 2017 4 Comments SQL Puzzle, , , , , , , , , , ,