Constructing an SQL query

I am using SQL and PL/SQL.

I have a table with a single column "name". The data in this column is a semicolon separated string. I want to count the number of elements in each row.

For example, if there are two rows in the table, one with the string 'smith;black;tiger'and one with the string 'x;y', I want the result of the query to be 3 for the first row and 2 for the second row.

How can I write a SQL query that will count the number of elements in a separated list开发者_运维百科 of values?

Your question is very hard to understand, but I think you are saying that you have a table with a column "name", and inside that column, each cell contains multiple values separated by semicolons. You want to count the number of semicolon-separated values in each row.

The PLSQL string functions could help you here, but really you are talking about writing program code to do this task (this isn't normally the job for a database query language). For example, you could use this trick to count the number of semicolons, and then add one:

LENGTH(name) - LENGTH(TRANSLATE(name,'x;','x')) + 1

But the point @Johan is making is that this is a bad way to structure your data. For example, it makes it impossible to lookup by name properly. You can't say where name == "smith", because the name doesn't equal "smith", it equals "smith;black;tiger". You'll have to do a substring search, saying where name like "smith", and that will be inefficient and wrong. What if I ask to look up the name "smi"? You'll say where name like "smi" which will incorrectly find that result -- there is no way to say that "smith" is in the table but "smi" is not, because both are substrings of "smith;black;tiger".

A much better solution, if you want an entry to have multiple names, is to give the entry a unique ID; say 123. (You can ask SQL to automatically generate unique IDs for table rows.) Then, have a separate table for names which maps back onto that row. So say you gave the "smith;black;tiger" row the ID 123 and the "x;y" row the ID 124. Now you would have another table NameMap with two columns:

name    |  entry
------------------
smith   |   123
black   |   123
tiger   |   123
x       |   124
y       |   124

Now you can look up names in that table and map them onto the name entries table with a join.

And you'll be able to answer your question: "how many names correspond to each row of the entry table" with a GROUP BY statement like this:

SELECT name, count(*) as value_count FROM NameMap GROUP BY name

You can hack this using this code

SQL code horror

SELECT name
       ,(LENGTH(COALESCE(vals,'')) 
        - LENGTH(TRANSLATE(COALESCE(vals,''), ';',''))) + 1 AS value_count
FROM table1
ORDER BY name

Remarks
CSV in a database is a very bad anti-pattern.
VALUES is a reserved word, it's a bad idea to name a column after a reserved word.