how to rearrange a data

I have a table like this:-

Item            Model
------------------------
A               10022009
B               10032006
C               05081997

I need to rearrange/convert the Model column into this format:-开发者_运维百科

Item            Model
------------------------
A               20090210
B               20060310
C               19970805

The Model column is character.

Thanks

You can try the following

UPDATE MyTable
SET Model = substr(Model, 5, 4) + substr(Model, 3, 2) + substr(Model, 1, 2)

The right way to do this, assuming those are date fields (and they certainly look like them), is to put that data into a date type column, not a string type column.

Then you can use the DBMS-provided date/time manipulation functions as they were meant to be used, including being able to extract them in the format and order that you want.

Normally, I would have proposed a simple textual change with substrings but, since you're going to change the data anyway, the best thing to do is bite the bullet and change the schema so all your problems disappear (not just one of them).

If you want to keep it as a string type, the syntax to use depends on your DBMS. It's likely to be one of the following:

substring (column, start, length)           # substr for Oracle, I think.
substring (column FROM start for length)