将基数N中的字符串表示形式的值转换为数字 [英] Convert value from string representation in base N to numeric

查看:257
本文介绍了将基数N中的字符串表示形式的值转换为数字的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

在我的数据库上,我使用特定符号的数字作为varchar值。有什么方法可以将这个值转换为带有所选符号的小数?



我基本上看到的应该是这样:

  SELECT to_integer_with_notation('d','20')

int4
---------
13

另一个示例:

  SELECT to_integer_with_notation('d3','23')

int4
---------
302
不幸的是,在PostgreSQL中没有内置的函数,但是在PostgreSQL中没有内置的函数,但是在PostgreSQL中没有内置的函数。可以写得相当容易:

  CREATE或REPLACE FUNCTION number_from_base(num TEXT,base INTEGER)
返回NUMERIC
LANGUAGE sql
IMMUTABLE
STRICT
AS $ function $
SELECT sum(exp * cn)
FROM(
SELECT base :: NUMERIC ^ row_number()OVER() - 1)exp,
CASE
当'0'和'9'的时候ascii(ch) - ascii('0')
WHEN ch BETWEEN' a'AND'z'THEN 10 + ascii(ch) - ascii('a')
END cn
FROM regexp_split_to_table(reverse(num)), $ b)sub
$ function $;

注意:我使用 numeric 作为返回类型,因为 int4 在许多情况下是不够的(使用更长的字符串输入)。



Edit :这里有一个示例反向函数,它可以将 bigint 转换为其在自定义基础中的文本表示:

  CREATE OR REPLACE FUNCTION number_to_base(num BIGINT,base INTEGER)
返回文本
LANGUAGE sql
IMMUTABLE
STRICT
AS $ function $
WITH RECURSIVE n(i,n,r)AS(
SELECT -1,num,0
UNION ALL
SELECT i + 1,n / base,(n%base):: INT
FROM n
WHERE n> 0

SELECT string_agg(ch,'')
FROM(
SELECT CASE
当0和9之间r :: TEXT
当10和35之间时chr(ascii('a')+ r - 10)
ELSE'%'
END ch
FROM n
WHERE i> = 0
ORDER BY i DESC
)ch
$ function $;

使用示例:

  SELECT number_to_base(1248,36); 

- + ---------------- +
- | number_to_base |
- + ---------------- +
- |哟|
- + ---------------- +


On my database i keep a numbers in specific notation as varchar values. Is there any way to typecast this values into decimals with selected notation?

What i basically looking here should look like this:

SELECT to_integer_with_notation('d', '20')

 int4
 ---------
 13

One more example:

SELECT to_integer_with_notation('d3', '23')

 int4
 ---------
 302

解决方案

Unfortunately, there is no built-in function for that in PostgreSQL, but can be written fairly easy:

CREATE OR REPLACE FUNCTION number_from_base(num TEXT, base INTEGER)
  RETURNS NUMERIC
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
SELECT sum(exp * cn)
FROM (
  SELECT base::NUMERIC ^ (row_number() OVER () - 1) exp,
         CASE
           WHEN ch BETWEEN '0' AND '9' THEN ascii(ch) - ascii('0')
           WHEN ch BETWEEN 'a' AND 'z' THEN 10 + ascii(ch) - ascii('a')
         END cn
  FROM regexp_split_to_table(reverse(lower(num)), '') ch(ch)
) sub
$function$;

Note: I used numeric as a return type, as int4 is not enough in many cases (with longer string input).

Edit: Here is a sample reverse function, which can convert a bigint to its text representation within a custom base:

CREATE OR REPLACE FUNCTION number_to_base(num BIGINT, base INTEGER)
  RETURNS TEXT
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
WITH RECURSIVE n(i, n, r) AS (
    SELECT -1, num, 0
  UNION ALL
    SELECT i + 1, n / base, (n % base)::INT
    FROM n
    WHERE n > 0
)
SELECT string_agg(ch, '')
FROM (
  SELECT CASE
           WHEN r BETWEEN 0 AND 9 THEN r::TEXT
           WHEN r BETWEEN 10 AND 35 THEN chr(ascii('a') + r - 10)
           ELSE '%'
         END ch
  FROM n
  WHERE i >= 0
  ORDER BY i DESC
) ch
$function$;

Example usage:

SELECT number_to_base(1248, 36);

-- +----------------+
-- | number_to_base |
-- +----------------+
-- | yo             |
-- +----------------+

这篇关于将基数N中的字符串表示形式的值转换为数字的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆