CODEWITHSUNDEEP
sql
Bob
Bob

Reputation: 885

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

Upvotes: 0

Views: 197

Answers (2)

paxdiablo
paxdiablo

Reputation: 882116

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)

Upvotes: 1

bobs
bobs

Reputation: 22204

You can try the following

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

Upvotes: 2

Related Questions