How to update the first digit of a number only?

Question

I need to update the first digit of numbers. For example, 3003.

I only want the first '3' to be changed to '2' and don't want the last digit '3' to be changed.

Something like the following faulty query:

update table
   set defaulttopicorder = replace(defaulttopicorder, left(defaulttopicorder,1), 2)
 where ....

Answer 1

This sounds like homework, so rather than giving the answer, how about a suggestion.

You can turn the number into a string, then just modify the first character in the varchar, but, when you increase it, remember that it may be incremented if you go from 9 to 10.

Another approach is to someone remove all the other digits, so if you have value: 3976, then have it be 3000, and increment then add back what you had removed.

Answer 2

Use STR(defaulttopicorder) to make a string out of the number, SUBSTRING around to take the part of it starting at index 2, + to concatenate the leading '2' with that substring.

Answer 3

Here's a quick inelegant approach:

DECLARE @MyInt INT = 5003
PRINT CAST(SUBSTRING(CAST(@MyInt AS VARCHAR(4)), 1, 1) + 1 AS CHAR(1)) + SUBSTRING(CAST(@MyInt AS VARCHAR(4)), 2, LEN(@MyInt) - 1)

Convert to an update as required. Tested on SQL08.

Answer 4

Assuming the defaulttopicorder column is a non-decimal, this will add 1 to the first digit of the number:

SET defaulttopicorder = defaulttopicorder + POWER(10, LEN(STR(defaulttopicorder)))

...so if you want to subtract 1 from the first digit:

SET defaulttopicorder = defaulttopicorder + -1 * POWER(10, LEN(STR(defaulttopicorder)))

Answer 5

Here's a slightly different approach, which doesn't involve turning it into a string (so it may be more efficient, although you'd have to test it to be certain).

• If you have a number N, then log₁₀(N) is the power that 10 would have to be raised to in order to get N.
• But if you round it down and raise 10 to that power (i.e. 10^{floor(log10(N))}) you'll get the place value of the leftmost digit.
• Now, multiply that place value by the amount you want to add or subtract from the digit (e.g. -1 or 1) and add the result to the original number.

So, given your example:

• N = 3003
• log₁₀(N) = approx. 3.477
• floor(3.477) = 3
• 10³ = 1000
• So, if you want to add 1 to the first digit, add 1 * 1000 (=1000) to the number; if you want to subtract 1, add -1 * 1000 (=-1000) to the number.

Of course, you'll want to be careful of rounding errors, which can pop up unexpectedly due to problems with representing decimal values as binary.