LENGTH() function is not working on a string in a column

Question

Hello everybody,  
  
maybe someone can help me, i am trying to find the number of items described in a string seperated by , (for example "x123A, x5894B, z567D" should resolve into 3 options)  
So i found a way to do this which is mainly comparing the length of the two strings minus the seperator plus 1.   
So lets say the column with the string of the options are a column called Options and i want to print the number of these options in "Number of Options"  
  
my approach is   
  
select "Options", (LENGTH(Options)-LENGTH(REPLACE(Options, ',',''))+1 as "Number of Options"   
  
This is working quite fine in other SQL or Tableau but Athena keeps throwing an error regarding Syntax Issues.  
The error i am getting is as follows:  
SYNTAX_ERROR: line 3:123: Unexpected parameters (array(varchar)) for function length. Expected: length(char(x)) , length(varchar(x)) , length(varbinary)   
which i think means the column is not handled as a single string for each row of the result but as a array of results.  
  
The same is true for some REG_EX instructions. I did not find any information regarding this.  
Any ideas or recommendations would be much appreciated.  
  
Kind Reagrds,  
Thomas

Answer

You can split the string into an array and then return the number of items:

```
select cardinality(split('x123A, x5894B, z567D', ',')) as options
```

When you run it against a table, you can replace 'x123A, x5894B, z567D' with the column name, but the above query returns '3' for me.

Answer

In case someone else is stumbling about this issue i found a solution.  
I had to first convert the array into a string and this worked perfectly with array_join i thought about casting it to the correct type using cast() before but without any success.  
I was able to get the length then via **length(array_join(Options, ','))**  
  
**select "Options", (LENGTH(array_join(Options,',')-LENGTH(array_join((Options, ''))+1 as "Number of Options"**  
  
Maybe this helps someone else cause it really took me forever to find a solution to this (Thanks to Guru Stron on Stackoverflow :) ).

Answer

you may have to use a combination of "regexp_like" (which evaluates every comma to true) along with "count" to count number of true occurrences. Here is a quick guide to presto regex documentation: https://prestodb.io/docs/0.217/functions/regexp.html

LENGTH() function is not working on a string in a column

Relevant content