Add a digit to the end of an SQL result-CodePudding

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

CodePudding user response：

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:

Num
251
672
1503

CodePudding user response：

You're working with numbers, so you can do Num*10 1 etc. Like this. fiddle

SELECT CASE WHEN Num BETWEEN    1 AND   50 THEN Num*10   1
            WHEN Num BETWEEN   51 AND   99 THEN Num*10   2
            WHEN Num BETWEEN  100 AND  199 THEN Num*10   3
            ELSE Num END AS Num
  FROM Numbers

That seems like it might be easier than string-casting and concatenating.

But you could do this if you really want strings. fiddle.

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 CONVERT(Num, CHAR) END AS Num
  FROM Numbers