-1

My Data:

TAn
Ants
TAr
Arm
TogA



UPDATE sample SET sample_data = REPLACE(sample_data , 'A', 'a');

The above shows my data and the SQL code i am using to change A to a. However i only want to change A to a on if A is not the first letter. How can i accomplish this in MySQL?

Dharman
  • 30,962
  • 25
  • 85
  • 135
CloudSeph
  • 863
  • 4
  • 15
  • 36
  • http://dev.mysql.com/doc/refman/5.7/en/regexp.html – Funk Forty Niner Dec 30 '16 at 01:37
  • 1
    Possible duplicate of [How to do a regular expression replace in MySQL?](http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql) – erik258 Dec 30 '16 at 01:39
  • What if A is the first letter and also another letter, like `AbcA`? Should that stay the same or become `Abca`? – Barmar Dec 30 '16 at 01:40
  • You'd probably use a regular expression to match a pattern like "any uppercase A that isn't at the beginning of the word", but this functionality doesn't exist as such in MySQL. I linked a duplicate that explains how you might do so. – erik258 Dec 30 '16 at 01:41
  • is the data in separate rows or just in one? question's unclear on a few levels – Funk Forty Niner Dec 30 '16 at 01:43

1 Answers1

2

Only call REPLACE on SUBSTRING(sample_data, 2)

UPDATE sample
SET sample_data = CONCAT(LEFT(sample_data, 1), REPLACE(SUBSTRING(sample_data, 2), 'A', 'a'))
Barmar
  • 741,623
  • 53
  • 500
  • 612