SQL string count

Question

I have a table in SQL. I have a column named CarMakes, it has the following contents:

Mercedes, BMW
Range Rover
Mazda, Toyota, Honda

I want to know how to return a number of CarMakes in each row.

Returned value should be 2, 1 and 3 respectively.

Do ***not*** store multiple values (delimited by something) in a single column. That is a ***very*** bad idea. You should re-design your data model. Then the query will be *very* simple. See here for more information: http://stackoverflow.com/q/3653462/330315 — a_horse_with_no_name, Nov 16 '12 at 15:33

score 1 · Accepted Answer · answered Nov 16 '12 at 15:16

1

This is a MySQL-based answer, so it may be inappropriate for your case if you're using something else. This assumes the values are comma-separated and in the same column (quite an assumption :) ):

SELECT car, (LENGTH(car)-LENGTH(REPLACE(car, ',', ''))+1) AS 'Counts'
FROM CarMakes

I'm sure this will run into issues somewhere, but it may work if your situation doesn't contain random commas. You can see the SQLFiddle here.

answered Nov 16 '12 at 15:16

RocketDonkey

33,047
7
75
83

@YerdenAbsattar: so, is your engine MySql? – Neolisk Nov 16 '12 at 15:38

score 0 · Answer 2 · answered Nov 16 '12 at 15:10

0

SELECT COUNT(CarMakes), CarMakes FROM cartable ORDER BY CarMakes ASC

answered Nov 16 '12 at 15:10

Sablefoste

3,680
3
35
52

Your solution assumes `CarMakes` is normalized and it's not. The query would return `(1, "Mercedes, BMW"), (1, "Range Rover"), (1,"Mazda, Toyota, Honda")`. – Neolisk Nov 16 '12 at 15:33
@Neolisk, that wasn't defined (or clear) in the original question. Thank goodness for updates and edits. As mentioned by others, the table should be normalized, making this the proper way to query. – Sablefoste Nov 16 '12 at 15:40

score 0 · Answer 3 · edited May 23 '17 at 12:12

0

Assuming T-SQL / MS SQL Server, you have two options:

String.Split - count array elements.
Find occurrence of character in String - count commas.

edited May 23 '17 at 12:12

Community

1
1

answered Nov 16 '12 at 15:35

Neolisk

23,880
16
72
135

SQL string count

3 Answers3