Question

Using SQL, write a table-valued function that:

-- takes a space delimited string as input (Input string will be a sentance ex: "The cat is on the chair and the bear is on the chair")

-- returns a table (word varchar(max), count int) (Output is a table that shows how many times each word appeared in the sentance)

Where each space-delimited “word” in the string appears in the table along with the number of times it appeared in the input string.

Additional Instructions: Don’t follow punctuation or language rules; a word is any sequence of non-spaces.

Space-delimited means “all spaces are ignored”, no word may contain spaces

There should not be a length 0 string.

Please include screenshots of the code and output, thanks so much!

Example Input: "The cat is on the chair and the bear is on the chair"

Example Output:

The 4

cat 1

is 2

on 2

chair 2

and 1

bear 1

Note: The user will input the sentence each time. (It is not hard coded)

Answer 1

DECLARE
      str VARCHAR2(100) := 'The cat is on the chair and the bear is on the chair';
      word_string VARCHAR2(40) := '';
      words NUMBER(4) := 1;
      s CHAR;
      current_index NUMBER(4);
      find_word VARCHAR2(40) := '';
      first_space INT(4) := 0;
      table_check INT(4) :=0; 

BEGIN
    SELECT count(*) INTO table_check FROM LETTERS; 
    IF table_check<=0 THEN
      EXECUTE IMMEDIATE 'create table LETTERS (word_letter VARCHAR2(20), frequency INT(4))';
    END IF;
    DELETE FROM LETTERS;
    FOR i IN 1..Length(str) LOOP
      s := Substr(str, i, 1);
      IF s != ' ' THEN
      word_string := CONCAT (word_string, s);
      END IF;
      IF ((s = ' ' or i = Length(str))) THEN
        Select count(*) into find_word from LETTERS where word_letter = lower(word_string);
            IF find_word = 0 then
                INSERT INTO LETTERS (word_letter, frequency) VALUES (lower(word_string), 1);
                word_string := '';
            ELSE
                UPDATE LETTERS set frequency = frequency+1 where word_letter = word_string;
                word_string :='';
            END IF;
      END IF;
END LOOP;

BEGIN
          FOR cursor1 IN (SELECT * FROM LETTERS) 
          LOOP
            dbms_output.put_line(cursor1.word_letter || ' ' || cursor1.frequency);
          END LOOP;
     END;
END;

Output:

Statement processed.
the 4
cat 1
is 2
on 2
chair 2
and 1
bear 1

Attatchments for reference:

Note: When code copied from above, student has to replace [NBSP] with space(' ') in the code, if found any.