I have a field in my database table in the following format:

100,30,443, etc.
Representing prices for 52(53) weeks of the year and comma delimited.

Now I need to check the price for a given week within my query.
The problem however is that the week number is variable.

Is there a way to count the number of non-consecutive occurances of a
character, in this case commas, in a regular expression?

I know ,{#} will check for # occurances of consecutive occurances of
#, but is there a similar way to check for non-consecutive?

Thank you in advance.

By way of example, I suspect that you would write

^([[:digit:]]+,){12}200,

If you want to check for the number 200 in the 13th position.

I've just tested out this on a table that I have:

select * from trees where whatusedfor rlike "^([^,]+,){2} greenhouses,";

and it correctly found "greenhouses" in the third comma separated field on my line in one of my records, but it didn't match "greenhouses" at any other input in the list.

Side issue.  Should this comma separated list really be one field in a database, or should you be using another table and relationships?  Just a thought ...

I have never used relationships :p so I wouldn't know. Basilcally I'm trying to keep the size of my database and the size of and number of tables to a minimum.

My predecessor, who set this database up originally, had two fields for every week of the year, one for the status of that week, one for the price of that week and a new table for each year. I have one table and only four fields to do the same job. The only downer being that two of those fields are comma seperated lists.

Any suggestions as to tweaks and improvements would be appreciated however.

Hmmm;  the best answer might be "if the data structure you have does the job for you, then it's fine".   In theory, neither the structure you describe from your predecessor, nor your structure, are ideal but it can take quite an effort to fix once the thing is set up.   I have a module on just this topic on one of our courses, but it's quite a chunk of stuff and too much to post on the forum.   Have a search for "Codd's principles of database normalisation" or something like that.

In summary ....

1. Eliminate repeating groups by putting each into a separate table and connecting them with a one-to-many relationship.

2. Eliminate functional dependencies on a partial key by putting the fields in a separate table from those that are dependent on the whole key.

3. Eliminate functional dependencies on non-key fields by putting them in a separate table. At this stage, all non-key fields are dependent on the key, the whole key and nothing but the key.

4. Separate independent multi-valued facts stored in one table into separate tables.  (This is your comma separated information!)

5. Break out data redundancy that is not covered by any of the previous.

Like I say, great theory and great if you use it BUT it's not the end of the world if you don't stick 100% to it on a small application

---