Question 20198 in Mysql Question posted in
The official MySQL documentation can be found here.

How to move the last digit of a nine digit number to the first in SQL – Mysql

I have a code in SQL

SELECT

NAME,
ROLL,
PHONE_NO

FROM
STUDENT

I want to add a new column with in the same extract which will have the same 9 digit phone number value but the last digit should be moved to the first place.

For example: if the value is 123456789 then the value of the new column in the same row should be 912345678

Tags:

2

Answers


  1. 0

    One approach is to use the modulus and integer division along with exponents to build the output you want:

    WITH t AS (
    SELECT 123456789 AS val
)

SELECT (val % 10) * POW(10, FLOOR(LOG10(val))) + FLOOR(val / 10)
FROM t;  -- 912345678

    For an explanation of the formula:

    (val % 10) * POW(10, FLOOR(LOG10(val))) = 9 * 100000000 = 900000000
                                                        +
FLOOR(val / 10)                                            12345678

                                                        = 912345678
    Login or Signup to reply.
  2. 0

    You could just use string manipulation:

    SELECT
    NAME, ROLL, PHONE_NO,
    CONCAT(RIGHT(PHONE_NO, 1), LEFT(PHONE_NO, LENGTH(PHONE_NO) - 1))
FROM STUDENT;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search