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.
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.
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
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)
SET Charss = SUBSTR(InputString, indexs, 1);
SET PrevChar = CASE WHEN INDEXs = 1 THEN ' '
ELSE SUBSTR(InputString, INDEXs - 1, 1)
IF PrevChar = ' ' AND Charss <> ' '
THEN SET WordCntt = WordCntt + 1;
SET Indexs = Indexs + 1;
SET WCount = WordCntt;
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
This might help you to resolve your issue.
SELECT SUBSTR(SENT,POS,1) SINGLE_CHAR
(SELECT 'COUNT WORDS FROM THIS SENATANCE FOR ME' SENT) A,
SELECT ROW_NUMBER() OVER (ORDER BY 1) Pos
WHERE POS <= CHAR_LENGTH(SENT)
WHERE SINGLE_CHAR = ' '
Thank you Kawish for sharing a robust version, It helps me learning another way to achieve this :)