Jump to content
Sign in to follow this  
Henry Olive

Sql-Add Number begining of an INT field

Recommended Posts

I wish a healthy day to everyone

 

MyTable

DOCNO (it is an Integer Field)

---------

1285

1286

......

 

I want to UPDATE my table and  add shortyear begining of DOCNO.   like below

DOCNO 

---------

211285

211286

......

 

Thank You
 

 

 

 

Share this post


Link to post
Posted (edited)

How about

Update mytable set docno = docno + 210000;

this modifies all records. If you want to limit the affected rows you would need to add a WHERE clause

Edited by baeckerg
more info

Share this post


Link to post
UPDATE mytable SET DOCNO = CAST(CONCAT('21', DOCNO) AS INT)

 

Share this post


Link to post
5 hours ago, baeckerg said:

Update mytable set docno = docno + 210000;

 

1 hour ago, julkas said:

UPDATE mytable SET DOCNO = CAST(CONCAT('21', DOCNO) AS INT)

Both solution are not complete as they will fail after the new year of 22.

 

@Henry Olive You didn't mention what DB server you are using as there is no one unified date functions for SQL across SQL DB providers.

For MS SQL Server you can use YEAR(GETDATE())

for MySql this will work YEAR(CURDATE())

for SQLite it does need to be formatted, please refer to this detailed page https://www.sqlite.org/lang_datefunc.html

...

 

after getting the year, either calculate the mod of it with 100 or take the last two digits (chars).

Share this post


Link to post
Posted (edited)

My solution was based on the fact that it is an integer field - therefore a simple "ADD" seamed to be the most straightforward way.

I am not working on interbase. Therefore my SQL is based on SQlite. There you could use

UPDATE mytable SET docno = CASE WHEN docno <2000 THEN docno + 210000 ELSE docno + 220000 END;

which would give you more flexibility. In SQLite you could also do 

UPDATE mytable SET docno = CASE WHEN docno <2000 then '21'|| DOCNO ELSE docno + 220000 END;

since SQLite internally handles the fields as strings. But case can give you flexibility in case :classic_cheerleader: you need it

Edited by baeckerg
Caps for keywords

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×