CODEWITHSUNDEEP
sqlsqlite
Gary
Gary

Reputation: 2859

Is there a better method of determining if an integer ends in specific two digits?

In a table having an integer primary key of indexRow in which the last two digits are currently 55, I'd like to change that to 50 but only if the column added is an integer value 55 and the indexRow ends in 55. I'm using SQLite.

I tested it as follows. Would you please tell me if this is the correct approach (if there is a better method) because I'd like to use it to run an update on the table?

Of course, I'll do it within a transaction and test before committing; but wanted to ask. I expected to have to use some math to determine which indexRows ended in 55, but converting to string seems quite easy.

select indexRow, indexRow-5, substring(format('%s', indexRow),-2)
from newSL
where added=55
  and substring(format('%s', indexRow),-2)='55'
limit 10;
indexRow     indexRow-5   substring(format('%s', indexRow),-2)
-----------  -----------  ------------------------------------
10080171455  10080171450  55                                  
10130031255  10130031250  55                                  
10140021655  10140021650  55                                  
10140080955  10140080950  55                                  
10240330155  10240330150  55                                  
10250230555  10250230550  55                                  
10270031155  10270031150  55                                  
10270290355  10270290350  55                                  
10300110355  10300110350  55                                  
10300110455  10300110450  55

Upvotes: 0

Views: 68

Answers (1)

Andy Lester
Andy Lester

Reputation: 93666

Yes, use the modulo operator, %. In the expression x % y, the result is the remainder of dividing x by y. Therefore, 4173 % 100 = 73.

Note that % is a math operator, just like * for multiplication and / for division, and is not related to using the % in the format function.

Upvotes: 2

Related Questions