Base 36 to Base 10 Conversion Using SQL Only

Base 36 to Base 10 conversion using SQL only

select sum(position_value) from
(
  select power(36,position-1) * case when digit between '0' and '9' 
                                     then to_number(digit)
                                     else 10 + ascii(digit) - ascii('A')
                                end
          as position_value
    from (
          select substr(input_string,length(input_string)+1-level,1) digit, 
                 level position
            from (select '01Z' input_string from dual)
            connect by level <= length(input_string)
         )
)

PostgreSQL: Is there a function that will convert a base-10 int into a base-36 string?

There are base-64 functions (such as encode) but nothing for base-36. But you could write one of your own or use this one:

CREATE OR REPLACE FUNCTION base36_encode(IN digits bigint, IN min_width int = 0) RETURNS varchar AS $$
DECLARE
    chars char[]; 
    ret varchar; 
    val bigint; 
BEGIN
    chars := ARRAY['0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
    val := digits; 
    ret := ''; 
    IF val < 0 THEN 
        val := val * -1; 
    END IF; 
    WHILE val != 0 LOOP 
        ret := chars[(val % 36)+1] || ret; 
        val := val / 36; 
    END LOOP;

    IF min_width > 0 AND char_length(ret) < min_width THEN 
        ret := lpad(ret, min_width, '0'); 
    END IF;

    RETURN ret;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

I think you should ask yourself if the database is the right place for dealing with this sort of data formatting though, presentational issues like this might be better handled closer to final viewing level of your stack.

Counting in base 36 with SQL

WITH
  num AS (SELECT TOP 36 ROW_NUMBER() OVER(ORDER BY (SELECT 1)) i FROM master.dbo.spt_values),
  chr AS (SELECT i,CASE WHEN i <= 10 THEN CHAR(i+47) ELSE CHAR (i+54) END c FROM num)
SELECT t3.c + t2.c + t1.c + t0.c
FROM chr t3, chr t2, chr t1, chr t0
ORDER BY t3.i, t2.i, t1.i, t0.i

BigQuery - Converting an ID from base 10 to base 36

In order to perform this conversion, you should use a JavaScript UDF with the JS method toString(36), which will allow you to convert to a String encoded in base 36.

The syntax is as follows:

CREATE TEMP FUNCTION tobase36(x NUMERIC)
RETURNS STRING
LANGUAGE js AS """
 return (x).toString(36);
""";
#test with sample data
WITH data AS (
SELECT 4503599684322375 AS base10
)
SELECT base10, tobase36(base10) AS base36 FROM data

And the output,

Row base10            base36    
1   4503599684322375  18ce54sjpl3

Convert base 10 integer to base 3, add base 3 number, and convert result back to base 10

--??

declare @startterm int = 20183,
        @addterms smallint = 3;

select (@startterm/10 + (@addterms+@startterm%10-1)/3) * 10 + isnull(nullif((@addterms+@startterm%10)%3, 0), 3) as endterm;

SQL server: How can I convert dates to base 36 annexed (3-digit)?

Here is the final answer, I get help from my colleague to complete it. Use "View" to store the temporary data and " DATEDIFF" to get numbers of days. Tq stackoverflow community!

WITH num
AS (SELECT TOP 36 ROW_NUMBER() OVER(ORDER BY (SELECT 1)) i FROM my_packing),
  chr AS (SELECT i,CASE WHEN i <= 10 THEN CHAR(i+47) ELSE CHAR (i+54) END c FROM num)
SELECT ROW_NUMBER () OVER (ORDER BY t2.c + t1.c +t0.c) as gg, t2.c +t1.c +t0.c
AS dateindex
INTO #t
FROM chr t2, chr t1, chr t0
ORDER BY  t2.i, t1.i, t0.i

SELECT  dateindex from #t
where gg in (select DATEDIFF(DAY, '2016/12/31', printDTime) AS DateDiff from my_packing(nolock)
where prdNo = '1234') 

Base 36 counter without I or O

An implementation of the suggestion in the question comments. As language is unimportant, here's a Ruby version:

class Integer
  def to_34_IO_shifted
    to_s(34).upcase.tr("IJKLMNOPQRSTUVWX", "JKLMNPQRSTUVWXYZ")
  end
end

class String
  def from_34_IO_shifted
    upcase.tr("JKLMNPQRSTUVWXYZIO", "IJKLMNOPQRSTUVWX10").to_i(34)
  end
end

puts 170.times.map { |x| x.to_34_IO_shifted }.join(' ')

x = 73644
x34 = x.to_34_IO_shifted
x10 = x34.from_34_IO_shifted
puts "\n#{x} -> '#{x34}' -> #{x10}"
puts "'10' -> #{'10'.from_34_IO_shifted}"
puts "'IO' -> #{'IO'.from_34_IO_shifted}"

Output:

0 1 2 3 4 5 6 7 8 9 A B C D E F G H J K L M N P Q R S T U V W X Y Z 10 11 12 13 14 15 16 17 18 19 1A 1B 1C 1D 1E 1F 1G 1H 1J 1K 1L 1M 1N 1P 1Q 1R 1S 1T 1U 1V 1W 1X 1Y 1Z 20 21 22 23 24 25 26 27 28 29 2A 2B 2C 2D 2E 2F 2G 2H 2J 2K 2L 2M 2N 2P 2Q 2R 2S 2T 2U 2V 2W 2X 2Y 2Z 30 31 32 33 34 35 36 37 38 39 3A 3B 3C 3D 3E 3F 3G 3H 3J 3K 3L 3M 3N 3P 3Q 3R 3S 3T 3U 3V 3W 3X 3Y 3Z 40 41 42 43 44 45 46 47 48 49 4A 4B 4C 4D 4E 4F 4G 4H 4J 4K 4L 4M 4N 4P 4Q 4R 4S 4T 4U 4V 4W 4X 4Y 4Z

73644 -> '1VQ0' -> 73644
'10' -> 34
'IO' -> 34

EDIT: made it so that I and O are interpreted as 1 and 0, in case someone does misread it.

---

Related Topics