2

I have a column in my mySQL database that stores dates in a comma separated list. Because of the need and dynamic structure of this database it needs to be a comma separated list with this format:

date,date,date,date,date,date,date,date

A real set may look like this:

2012-01-01,2012-03-12,,,,,,

I need to be able to count the number of dates in this list in my query. For the example set above the number of dates in the set would be two (2012-01-01 & 2012-03-12). For my purposes I need to know if there are 8 dates in this list or not. I have found different solutions around and I had success with something like this giving me the number of time the number 1 was in a string:

LENGTH(_COLUMN_) - LENGTH(REPLACE(_COLUMN_, '1', ''))

If I changed the '1' to a regex then I couldn't get it to work. Part of the problem is I also could not find a solution for a regex that would find exactly ####/##/## and not any other variations of a date.

I am still learning mySQL and regex and am looking for some help.

THANKS.

Jonny Jesperson
  • 103
  • 5
  • 1
    I absolutely can't imagine a single reason, why there would be a need to have a comma separated list in a column. And no offense but where is this "dynamic"? Change DB design! – fancyPants Sep 05 '12 at 21:56
  • 100% agree with @tombom; but as a purely intellectual exercise, I suppose you could count hyphens and divide by two... – eggyal Sep 05 '12 at 22:15
  • @tombom We have to gather a lot of dates for temporary forms. I didn't want to make a new column for the 26 different dates that we needed to gather. Instead I tried to use three columns (representing the 3 forms) that contain a comma separated list of the dates so we didn't have to have 26 different columns gumming up out table. When there are a certain number of dates in each column then the column/form is finished. Any help with structure is appreciated! – Jonny Jesperson Sep 06 '12 at 15:24
  • @JonnyJesperson Create a new question and show your current db schema. And accept one of the answers below. – fancyPants Sep 06 '12 at 15:37

2 Answers2

0

Would it be possible for you to do this with php?

Get the result into a variable and then:

$variable=date,date,date,date,date,date,date,date;
$array=explode(",",$variable);
$count=count($array);

What this is doing is splitting up your string by the commas and inserting it into an array with explode and then count is counting the amount of elements in the array.

Explode, Count

Also for your other solution:

LENGTH(_COLUMN_) - LENGTH(REPLACE(_COLUMN_, '1', ''))

Would it not be possible simply to do:

LENGTH(_COLUMN_) - LENGTH(REPLACE(_COLUMN_, ',', ''))

and get a count of the commas, since you say the format will be comma delimited dates with hyphens as separators, if you get a count of 7 commas you'll know you have 8 dates.

mrmryb
  • 1,479
  • 1
  • 12
  • 18
0

Wouldn't a string with 8 dates in it be exactly 87 characters long?

If you're concerned about the actual values in the string, split them out.

Oh, and redesign your schema.

Community
  • 1
  • 1
Alain Collins
  • 16,268
  • 2
  • 32
  • 55