SQL Replace command

[Brian]

I am trying to do a replace to mass update a lowercase/uppercase data entry
error. Example of error; I needed all these numbers to be like 99A555,
99A556, 99A557 and so on. Instead they were entered as 99a555, 99a556,
99a557 so on ...... How can this be fixed using SQL?

 

[Robert Morley]

The simple variant of the command you're looking for would be:

UPDATE MyTable
SET MyField = UPPER(MyField)

If you want to be really efficient, though, you could take it a step further
and do this, which only updates those fields that actually need it:

UPDATE MyTable
SET MyField = UPPER(MyField)
WHERE MyField COLLATE SQL_Latin1_General_Cp437_BIN <> UPPER(MyField)

There might be a better way of doing the above, or a better collation to
use, but that'll get the job done, provided there's no international
characters in that field.



Rob

 

[Susie Johnson]

I think that you'd be better off with this

UPDATE MyTable
SET MyField = UPPER(MyField)
WHERE CHECKSUM(MyField) <> BINARY_CHECKSUM(MyField)