Count the number of words in a phrase using Teradata SQL

Database

Count the number of words in a phrase using Teradata SQL

Hi,

what would be the code to count the number of words in a phase using Teradata SQL without using the oreplace function as I do not have access to this.

Thanks in advance.

Regards,

Sree

Tags (3)
5 REPLIES
Enthusiast

Re: Count the number of words in a phrase using Teradata SQL

Hi Sreechandn,

If you are in TD Version 14, then you can make use of  regex_replace function along with length function.

Another easier way, I always follow is use a procedure, because I can handle any complex logic, whatever maybe the case.

Cheers,

Raja

Enthusiast

Re: Count the number of words in a phrase using Teradata SQL

Hi Sree,

I have created a stored procedure to count the number of words in a string, hope it will help you!

REPLACE PROCEDURE SAMPLES.WordCount
(
IN InputString VARCHAR(4000) ,
OUT WCount INT
)

BEGIN

DECLARE Indexs INT;
DECLARE Charss CHAR(1);
DECLARE PrevChar CHAR(1);
DECLARE WordCntt INT;

SET Indexs = 1;
SET WordCntt = 0;

WHILE Indexs <= CHARACTER_LENGTH(InputString)
DO
BEGIN
SET Charss = SUBSTR(InputString, indexs, 1);
SET PrevChar = CASE WHEN INDEXs = 1 THEN ' '
ELSE SUBSTR(InputString, INDEXs - 1, 1)
END;

IF PrevChar = ' ' AND Charss <> ' '
THEN SET WordCntt = WordCntt + 1;
END IF;

SET Indexs = Indexs + 1;
END;
END WHILE
;

SET WCount = WordCntt;

END;
Khurram
Enthusiast

Re: Count the number of words in a phrase using Teradata SQL

Hi Sreechand,

Sorry, I did miss  the subject line   :)  "...using-teradata-sql"..

Here it is :

 SELECT (char_length('Hi How are you doing today') -char_length(REGEXP_REPLACE('Hi How are you doing today', '[\s]', '', 1, 0, 'c'))) +1

Cheers,

Raja

Enthusiast

Re: Count the number of words in a phrase using Teradata SQL

Sree,

This might help you to resolve your issue.

SELECT COUNT(*)+1 
FROM
(
SELECT SUBSTR(SENT,POS,1) SINGLE_CHAR
FROM
(SELECT 'COUNT WORDS FROM THIS SENATANCE FOR ME' SENT) A,
(
SELECT ROW_NUMBER() OVER (ORDER BY 1) Pos
FROM sys_calendar.CALENDAR
) B
WHERE POS <= CHAR_LENGTH(SENT)
) B
WHERE SINGLE_CHAR = ' '
Enthusiast

Re: Count the number of words in a phrase using Teradata SQL

Thank you Kawish for sharing a robust version, It helps me learning another way to achieve this :)

Khurram