0

Is there any way to check for a regex expression in a comma separated values column?

I have a column named storeId with the following values EMP_0345,00345,OPS and I need to get only the storeid with no alpha numeric characters in it.

I am able to get the valid store_ids with the regex REGEXP '^[0-9]+$' but how do get the values in a comma separated values column?

Learner2011
  • 287
  • 2
  • 6
  • 25

2 Answers2

0

You violated a dogma of database design: Never ever store more than one value in a single field, if you need to access them separatly

Even if you manage to REGEX your way around this, you will run into massive performance troubles. The correct way to tackle this ist to move the contents of the CSV column into rows of a join table, then simply match against the single values of that join table.

Eugen Rieck
  • 64,175
  • 10
  • 70
  • 92
0

Your database design is flawed - given your current request.
Whether you should (try to) convert that column into several columns in the current (or a different) table, or rather into rows in a different table does primarily depend on whether or not there is some structure in that column's data.
With some inherent structure, you could use something like

SELECT
  storeId
  , SUBSTRING_INDEX(storeId, ',', 1) AS some_column
  , SUBSTRING_INDEX(SUBSTRING_INDEX(storeId, ',', 2), ',', -1) AS store_id
  , SUBSTRING_INDEX(storeId, ',', -1) AS another_column
FROM T
WHERE storeId REGEXP '^[^,]+,[0-9]+,[^,]+$'
;

to separate the values (and potentially populate newly added columns). The WHEREclause would allow to differentiate between sets of rows with specific arrangements of values in the column in question.

See in action / more detail: SQLFiddle.

Please comment if and as adjustment / further detail is required, or update your request to provide more detailed input.

Abecee
  • 2,365
  • 2
  • 12
  • 20
  • @Abecee.Thanks for your response. INSERT INTO T (storeId) VALUES ('EMP_0345,00345,OPS'), ('EMP_0A45,00A45,EPS'), ('EMP_0346,00346,APS,XYZ'); I need to get only 00345,00346 when I query table T since those are the only valid storeids. – Learner2011 Feb 12 '15 at 19:07
  • And you have always at least two commas in your string - with the "store_id" between the first and the second one? If not, please, give examples of the cases to handle. Are you at all interested in what the (so far) suggested solution puts into "some_column" and "another_column" - or do you just want the "store_id" `SELECT`ed? Anything else, not mentioned so far but to keep in mind? – Abecee Feb 13 '15 at 00:58