Ian Brindley Ian Brindley - 5 months ago 10
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.

VolumeNumber
------------
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.

Answer

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

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