All Forums Database
sreechandn 1 post Joined 11/13
27 Nov 2013
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

Raja_KT 1246 posts Joined 07/09
27 Nov 2013

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
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

M.Saeed Khurram 544 posts Joined 09/12
28 Nov 2013

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

Raja_KT 1246 posts Joined 07/09
28 Nov 2013

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Kawish_Siddiqui 37 posts Joined 03/07
03 Dec 2013

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 = ' '

 

Kawish Siddiqui -

M.Saeed Khurram 544 posts Joined 09/12
03 Dec 2013

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

Khurram

You must sign in to leave a comment.