Ian Brindley Ian Brindley - 4 months ago 6x
MySQL Question

Rectify miss formating of text in mysql column

My problem is that I have column where a large data dump has been slightly miss-formatted.

Volume 1Numbers 1 & 2
Volume 1Numbers 1 & 2
Volume XIINumbers 3 & 4
Volume XIINumbers 3 & 3

The problem is that there is a space missing before the word Number. i.e (Volume 1 Numbers 1 & 2)

There are far to many rows for me to rectify this problem manually.


Assuming that "Numbers" only appears once in VolumeNumber, something like this should do it:

UPDATE SomeTable
    VolumeNumber = REPLACE(VolumeNumber, 'Numbers ', ' Numbers ')
    VolumeNumber NOT LIKE '% Numbers%'