将基数N中的字符串表示形式的值转换为数字 [英] Convert value from string representation in base N to numeric
问题描述
在我的数据库上,我使用特定符号的数字作为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屋!