Select Replace with multiple replacements

1

I have a table of names, that have been converted to upper/lower case but suffixes like II, III, and IV got converted to Ii, Iii and Iv.
So for example,

select REPLACE(name,' iv',' IV') from Person where name like '% iv'
fixes the one with suffix "IV"

But how do I fix all 3 in one Select statement?

2 
SELECT  Replace(Replace(Replace(NameField, ' iv', ' IV'), ' ii', ' II'), ' iii', ' III') CorrectedName
FROM TableName
Where Right(NameField, 3) IN (' ii', 'iv') or Right(NameField, 4) = ' iii');
1 Like
3

When I have nested REPLACE like this I write then thus:

Replace(Replace(Replace(
    NameField
    , ' iv', ' IV')
    , ' ii', ' II')
    , ' iii', ' III')

as I think that makes it easier to "add more", or rearrange the order (if that turns out to be important) over time.

Note that those REPLACEs will ALSO change "Ivan" to "IVan", and so on. You might be able to improve on that by adding a prefix space, but there is still a risk that matches something within the NameField

Replace(Replace(Replace(
    NameField
    , '  iv', '  IV')
    , '  ii', '  II')
    , '  iii', '  III')

if the suffixes are always at the end of the string? then you could probably further "ring-fence" the replacements like this:

Replace(Replace(Replace(Replace(
    NameField+'~' --- where "~" is a character which does NOT occur anywhere in the NameField
    , '  iv~', '  IV~')
    , '  ii~', '  II~')
    , '  iii~', '  III~')
    , '~', '')
1 Like
4

thank you.

5

Thank you.

6

I changed the last line, in my earlier post, to Ahem!! ... "Fix a bug" :slight_smile: