Mod Negative Numbers in SQL Just Like Excel

Mod negative numbers in SQL just like excel

This will give a result between 0 and n - 1 for both positive and negative values of x:

((x % n) + n) % n

Why is Modulo in MySQL (with negative number) giving unexpected results?

You could implement your own version of MOD using division as in this question but probably the simplest solution is to perform MOD twice, adding 50 between operations:

SELECT MOD(MOD(input, 50)+50, 50)

This will give you correct results regardless of the input value.

Convert negative data into positive data in SQL Server

You are thinking in the function ABS, that gives you the absolute value of numeric data.

SELECT ABS(a) AS AbsoluteA, ABS(b) AS AbsoluteB
FROM YourTable

VB6 Mod function gives incorrect values with negative values

mod is not the same in all languages, especially for negative numbers. VB6 (and a whole load of other compilers like C, C++, C#, Java) takes the Fortran interpretation which is the remainder after dividing. Mathematically, this is the wrong interpretation if the number is negative. What you have is

5184326 + -6034670 = -850344
-850344 mod 16777216 = -850344 

Python and excel take the correct interpretation of modulo where the result is always positive. This takes an extra step i.e.

-850344 + 16777216 = 15926872

How can I modulo when my numbers start from 1, not zero?

Just subtract 1 and add 1 afterwards.

In most programming languages, you need to watch out when finding a "previous" value, because for negative numbers, modulo does not work as you want in this case: it returns a negative number.

Here's the C/C++ version:

int next(int i, int m, int n) { return (i + m - 1) % n + 1; }
int prev(int i, int m, int n) { return (i - m + n - 1) % n + 1; }

However, in Perl modulo always returns a positive value (at least when the second operand is a positive integer). Basically it does what you want. So you can write the following and leave out the + $_[2]:

sub nxt { ($_[0] + $_[1] - 1) % $_[2] + 1; }
sub prv { ($_[0] - $_[1] - 1) % $_[2] + 1; }

SQL: Is there a possibility to convert numbers (1,2,3,4...) to letters (A,B,C,D...)

Try this:

SELECT
   Letters = Char(64 + T.Num),
   T.Col1,
   T.Col2
FROM
   dbo.YourTable T
;

Just be aware that when you get to 27 (past Z), things are going to get interesting, and not useful.

If you wanted to start doubling up letters, as in ... X, Y, Z, AA, AB, AC, AD ... then it's going to get a bit trickier. This works in all versions of SQL Server. The SELECT clauses are just an alternate to a CASE statement (and 2 characters shorter, each).

SELECT
   *,
   LetterCode =
      Coalesce((SELECT Char(65 + (N.Num - 475255) / 456976 % 26) WHERE N.Num >= 475255), '')
      + Coalesce((SELECT Char(65 + (N.Num - 18279) / 17576 % 26) WHERE N.Num >= 18279), '')
      + Coalesce((SELECT Char(65 + (N.Num - 703) / 676 % 26) WHERE N.Num >= 703), '')
      + Coalesce((SELECT Char(65 + (N.Num - 27) / 26 % 26) WHERE N.Num >= 27), '')
      + (SELECT Char(65 + (N.Num - 1) % 26))
FROM dbo.YourTable N
ORDER BY N.Num
;

See a Live Demo at SQL Fiddle

(Demo for SQL 2008 and up, note that I use Dense_Rank() to simulate a series of numbers)

This will work from A to ZZZZZ, representing the values 1 to 12356630. The reason for all the craziness above instead of a more simple expression is because A doesn't simply represent 0, here. Before each threshold when the sequence kicks over to the next letter A added to the front, there is in effect a hidden, blank, digit--but it's not used again. So 5 letters long is not 26^5 combinations, it's 26 + 26^2 + 26^3 + 26^4 + 26^5!

It took some REAL tinkering to get this code working right... I hope you or someone appreciates it! This can easily be extended to more letters just by adding another letter-generating expression with the right values.

Since it appears I'm now square in the middle of a proof-of-manliness match, I did some performance testing. A WHILE loop is to me not a great way to compare performance because my query is designed to run against an entire set of rows at once. It doesn't make sense to me to run it a million times against one row (basically forcing it into virtual-UDF land) when it can be run once against a million rows, which is the use case scenario given by the OP for performing this against a large rowset. So here's the script to test against 1,000,000 rows (test script requires SQL Server 2005 and up).

DECLARE
   @Buffer varchar(16),
   @Start datetime;

SET @Start = GetDate();
WITH A (N) AS (SELECT 1 FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) A (N)),
B (N) AS (SELECT 1 FROM A, A X),
C (N) AS (SELECT 1 FROM B, B X),
D (N) AS (SELECT 1 FROM C, B X),
N (Num) AS (SELECT Row_Number() OVER (ORDER BY (SELECT 1)) FROM D)
SELECT @Buffer = dbo.HinkyBase26(N.Num)
FROM N
;
SELECT [HABO Elapsed Milliseconds] = DateDiff( ms, @Start, GetDate());

SET @Start = GetDate();
WITH A (N) AS (SELECT 1 FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) A (N)),
B (N) AS (SELECT 1 FROM A, A X),
C (N) AS (SELECT 1 FROM B, B X),
D (N) AS (SELECT 1 FROM C, B X),
N (Num) AS (SELECT Row_Number() OVER (ORDER BY (SELECT 1)) FROM D)
SELECT
   @Buffer =
      Coalesce((SELECT Char(65 + (N.Num - 475255) / 456976 % 26) WHERE N.Num >= 475255), '')
      + Coalesce((SELECT Char(65 + (N.Num - 18279) / 17576 % 26) WHERE N.Num >= 18279), '')
      + Coalesce((SELECT Char(65 + (N.Num - 703) / 676 % 26) WHERE N.Num >= 703), '')
      + Coalesce((SELECT Char(65 + (N.Num - 27) / 26 % 26) WHERE N.Num >= 27), '')
      + (SELECT Char(65 + (N.Num - 1) % 26))   
FROM N
;
SELECT [ErikE Elapsed Milliseconds] = DateDiff( ms, @Start, GetDate());

And the results:

UDF: 17093 ms
ErikE: 12056 ms

Original Query

I initially did this a "fun" way by generating 1 row per letter and pivot-concatenating using XML, but while it was indeed fun, it proved to be slow. Here is that version for posterity (SQL 2005 and up required for the Dense_Rank, but will work in SQL 2000 for just converting numbers to letters):

WITH Ranks AS (
   SELECT
      Num = Dense_Rank() OVER (ORDER BY T.Sequence),
      T.Col1,
      T.Col2
   FROM
      dbo.YourTable T
)
SELECT
   *,
   LetterCode =
      (
         SELECT Char(65 + (R.Num - X.Low) / X.Div % 26)
         FROM
            (
               SELECT 18279, 475254, 17576
               UNION ALL SELECT 703, 18278, 676
               UNION ALL SELECT 27, 702, 26
               UNION ALL SELECT 1, 26, 1
            ) X (Low, High, Div)      
         WHERE R.Num >= X.Low
         FOR XML PATH(''), TYPE
      ).value('.[1]', 'varchar(4)')
FROM Ranks R
ORDER BY R.Num
;

See a Live Demo at SQL Fiddle

---

Related Topics