CODEWITHSUNDEEP
teradata
Borat Sagddiev
Borat Sagddiev

Reputation: 837

How to count the number of commas in a string?

I use teradata. No access to functions, UDFs, stored procedures

Table

id  attribute
1   strength, power
2   ring, wig, puff
3   strength, ring
4   puff, wig
5   power

I need to count the number of attributes

id  att_number
1   2
2   3
3   2
4   2
5   1

How can I do this?

Upvotes: 0

Views: 10897

Answers (4)

Krunal Patange
Krunal Patange

Reputation: 21

SELECT colname
,CHARACTERS(TRIM(colname)) -CHARACTERS(TRIM(OREPLACE(colname,',',''))) +1
 SAMPLE 100  FROM tablename

Upvotes: 2

Puneet Gupta
Puneet Gupta

Reputation: 11

Can use a much simpler and efficient one liner code as below:

select length(trim(regexp_replace(Column_Name,'[^,]+','')));

Upvotes: 1

dnoeth
dnoeth

Reputation: 60492

Of course your site should install at least the oTranslate and oReplace UDFs. Then it's a simple 

CHAR_LENGTH(col) - CHAR_LENGTH(OTRANSLATE(col, ',',''))

Without those functions it's a pain in the <insert your favourite body part here>. Simply try to implement a REPLACE without oREPLACE, complex SQL with a horrible performance.

In your case it's a bit easier. If the maximum number is 12 the most efficient way will be a straight:

CASE
   WHEN col LIKE '%,%,%,%,%,%,%,%,%,%,%,%,%' THEN 12
   WHEN col LIKE '%,%,%,%,%,%,%,%,%,%,%,%'   THEN 11
   WHEN col LIKE '%,%,%,%,%,%,%,%,%,%,%'     THEN 10
   WHEN col LIKE '%,%,%,%,%,%,%,%,%,%'       THEN  9
   WHEN col LIKE '%,%,%,%,%,%,%,%,%'         THEN  8
   WHEN col LIKE '%,%,%,%,%,%,%,%'           THEN  7
   WHEN col LIKE '%,%,%,%,%,%,%'             THEN  6
   WHEN col LIKE '%,%,%,%,%,%'               THEN  5
   WHEN col LIKE '%,%,%,%,%'                 THEN  4
   WHEN col LIKE '%,%,%,%'                   THEN  3
   WHEN col LIKE '%,%,%'                     THEN  2
   WHEN col LIKE '%,%'                       THEN  1
   ELSE                                            0
END

No need for nested CASE/POSITION/SUBSTRING.

Upvotes: 4

user1509107
user1509107

Reputation:

Using only index and substring functions, you would need a nested case statement which checks for a max of 12 attributes as show below. You would need to further nest the case statement to able to count upto 12 attributes, currently it can only count to a max of 2 attributes. [note: mytext=attributes]

SELECT id, mytext
,case 
when index(mytext, ',')= 0 then 0
when index(mytext, ',')>0 then  
                                    case
                                    when index(substring(mytext,  index(mytext, ','), length(mytext)), ',')=0 then 1
                                    when index(substring(mytext,  index(mytext, ','), length(mytext)), ',')>0 then 2
                                    end
end
from mydb.sd_test
order by id;

Upvotes: 3

Related Questions