Add a digit to the end of an SQL result

April 7, 2022

I want the result to add number in the end in specific cases.

Like: if result is between 1 and 50, add 1.
If result is between 51 and 99, add 2 to the end.
If result is between 100 and 200, add 3 to the end.

Like:
Result = 25, do it 251.
Result 67, do it 672.
Result is 150, do it 1503.

I have created a table but the cases don’t seem to work. How would I add a digit in specific cases?

CREATE TABLE Numbers(
    Num INT
);

INSERT Numbers VALUES('12');
INSERT Numbers VALUES('112');
INSERT Numbers VALUES('12');
INSERT Numbers VALUES('122');
INSERT Numbers VALUES('1');
INSERT Numbers VALUES('2');
INSERT Numbers VALUES('12345678');
INSERT Numbers VALUES('12345');
    
    
SELECT * FROM Numbers;

SELECT RIGHT('15'+ CONVERT(VARCHAR,Num),6) AS NUM FROM Numbers;

SELECT LEFT(REPLICATE('0', 10) + CONVERT(VARCHAR, Num), 6) AS NUM FROM Numbers;

SELECT RIGHT('0' + CAST(Num AS VARCHAR(2)), 2) FROM Numbers

SELECT
    CASE
        WHEN Num BETWEEN 1 AND 99
            THEN LEFT ('00' + CAST(Num AS VARCHAR(2)), 2)
        ELSE
            CAST(Num AS VARCHAR(10))
    END
 FROM Numbers

>Solution :

Since you’re already using varchar on these values, I’d use concat – which simply mergs strings together. In this case you simply select what you want to merge, with what. Documentation on Concat() here.

Fiddle: https://www.db-fiddle.com/f/at2fqinuEao3b8coRSydTD/1

SELECT 
  CASE WHEN Num BETWEEN 1 AND 50 
      THEN concat(Num, '1')
  WHEN Num BETWEEN 51 AND 99 
      THEN concat(Num, '2')
  WHEN Num BETWEEN 100 AND 199 
      THEN concat(Num, '3')
  ELSE Num END AS Num
FROM Numbers

In the examples of your 25,67 and 150 – this is the result: