sql - Pulling specific digits from place values

Question

I'm wondering if it's possible to pull the digits at specific place values in a number with SQL.

For example, the number 7654321 is stored in the database. I want to pull out the number at the one thousands' place, which in this case is 4.

Thanks in advance for the advice!

Answer 1

You don't say what RDBMS you are using.

If SQL Server you could do

SELECT (7654321/1000)%10

To divide by 1000 with integer division to get 7654 and then calculate modulo 10.

Something similar should be possible in all DBMSs though the exact syntax may vary. E.g potentially something like

SELECT MOD(TRUNC(7654321/1000),10) 
FROM DUAL;

Answer 2

I'm assuming you are using SQL Server! you can try this:

select(
    LEFT(
        (RIGHT(7654321, len(7654321) - 3)), 
            len((RIGHT(7654321, len(7654321) - 3)) )- 3)
    )

First I removed 3 digits from left(7654321 --> 4321 ) then removed 3 digits from right(4321 --> 4). if you feel this method difficult you can try this:

select  (LEFT(7654321, len(7654321) - 3) %10)

HTH