Home 2017 October SQL Puzzle: SQL Advance Query – Split the String and generate count of Each part

SQL Puzzle: SQL Advance Query – Split the String and generate count of Each part

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

Check the below input data and expected output to split the string and generate a count of each string. Split the string by semi colon.

Input Data:

Expected Output:

Create a table with data:

Solution 1:

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

Anvesh Patel
SQL Puzzle: SQL Advance Query - Use STUFF() and form the aggregate of columnsSQL Puzzle: SQL Advance Query - Check number is an Integer or not an Integer
Comments: 5
  1. Dinesh.IS
    October 24, 2017 at 4:55 am

    Declare @SplitDetails Table(StringValue VarChar(5000));
    Declare @StringValue VarChar(5000),@Len As Int
    Declare Cur Cursor For Select Str1 From TBL_SplitData
    Open Cur
    Fetch Next From Cur Into @StringValue
    While @@Fetch_Status=0
    Begin

    While Len(@StringValue)>0
    Begin

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

    Insert Into @SplitDetails
    (StringValue)
    Values(SubString(@StringValue,1,@Len))

    Set @StringValue=(Case Len(@StringValue)-@Len When 0 Then Null Else Right(@StringValue,Len(@StringValue)-@Len-1) End)
    End
    Fetch Next From Cur Into @StringValue
    End
    CLose Cur
    Deallocate Cur

    Select StringValue,Count(*) As StringCounts From @SplitDetails
    Group By StringValue

    ReplyCancel
  2. Dinesh.IS
    October 24, 2017 at 4:55 am

    Declare @SplitDetails Table(StringValue VarChar(5000));
    Declare @StringValue VarChar(5000)=”,@Len As Int

    Select @StringValue+=’;’+Str1 From (Select Str1 From TBL_SplitData ) A
    Set @StringValue=STUFF(@StringValue,1,1,”)

    While Len(@StringValue)>0
    Begin

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

    Insert Into @SplitDetails
    (StringValue)
    Values(SubString(@StringValue,1,@Len))

    Set @StringValue=(Case Len(@StringValue)-@Len When 0 Then Null Else Right(@StringValue,Len(@StringValue)-@Len-1) End)

    End

    Select StringValue,Count(*) As StringCounts From @SplitDetails
    Group By StringValue

    ReplyCancel
    • ankush g
      September 12, 2018 at 10:27 am

      Select NAME, count(NAME) from (
      Select DISTINCT
      trim(regexp_substr(str1, ‘[^;]+’, 1, level)) Name, level
      FROM tbl_SplitData
      connect by regexp_substr(name, ‘[^;]+’, 1, level) is not null)foo
      GROUP BY NAME

      ReplyCancel
  3. Ankush
    September 12, 2018 at 10:25 am

    What do u say about this?

    Select NAME, count(NAME) from (
    Select DISTINCT
    trim(regexp_substr(str1, ‘[^;]+’, 1, level)) Name, level
    FROM tbl_SplitData
    connect by regexp_substr(name, ‘[^;]+’, 1, level) is not null)foo
    GROUP BY NAME

    ReplyCancel
  4. Ankush
    September 12, 2018 at 10:26 am

    Select NAME, count(NAME) from (
    Select DISTINCT
    trim(regexp_substr(str1, ‘[^;]+’, 1, level)) Name, level
    FROM tbl_SplitData
    connect by regexp_substr(name, ‘[^;]+’, 1, level) is not null)foo
    GROUP BY NAME

    ReplyCancel

Leave a Reply

CAPTCHA

*

Anvesh Patel
Anvesh Patel

Database Engineer

October 21, 2017 5 Comments SQL Puzzle, , , , , , , , , , ,