0

enter image description here

I have a column in my table which has below values and I need the o/p in the following format: (Example: S-S-661679. It should in the format S-######).

The third o/p below is the required o/p in the correct format.

Column Value are like:
'S-S-652235'
'S-xjq113465'
**'S-652235'**
'-641408'

I am using MySQL to query this data from the table. Few columns have the correct value but there are alphabets also and the final o/p should have just S-######. I was thinking of using Regular Expression but could not figure out how to use that in SELECT Clause, all the examples I have seen, I see that it can only be used in WHERE Clause.

I am using below code: **CONCAT('S-', REPLACE(REPLACE(seminar_id, ',', ''), 's', ''))** in the select clause, but it is replicating the 'S-' which is already present in some columns. I am trying to figure out how to replace multiple 'S-' and also the alphabetical characters from the values in the columns.

(Example: S-S-661679. It should in the format S-######).

The third o/p below is the required o/p in the correct format and the highlighted in the image attached is the correct format.

Column Value are like:
'S-S-652235'
'S-xjq113465'
**'S-652235'**
'-641408'
Vishwas
  • 343
  • 2
  • 13
Sukrit Sen
  • 23
  • 1
  • 6
  • To clarify, do you mean you want to select all rows, but reformat the value to match the format of `S-652235`? So `S-S-652235` would become `S-652235`, `S-xjq113465` would become `S-113465`, `S-652235` would be returned as-is, and `-641408` would become `S-641408`? – WOUNDEDStevenJones Oct 07 '19 at 18:26
  • Yes @WOUNDEDStevenJones, this is exactly how I am looking for the data to be! – Sukrit Sen Oct 07 '19 at 18:28
  • getting formats 1, 3, and 4 to just `######` and then prepending `S-` seems to be trivial (and it looks like you're basically there: `REPLACE(REPLACE(val, 'S-', ''), '-', '')`). So in order to fix format #2 check out https://stackoverflow.com/questions/37268248/how-to-get-only-digits-from-string-in-mysql. Then you'd end up with a digits-only string, and similarly you can just prepend `S-` and you have everything in the same format. – WOUNDEDStevenJones Oct 07 '19 at 19:07
  • What version of MySQL? 8.0 has `REGEXP_REPLACE()`, which makes it easy. – Rick James Oct 15 '19 at 00:55

0 Answers0