DIGITS
适用于 ✅ 开源版 ✅ 专业版 ✅ 企业版
DIGITS() 函数允许将数字转换为 0 填充的字符串，从而在将这些字符串与其他字符串连接时，可以更容易地根据数值对它们进行排序。 填充取决于数字的数据类型、精度和标度。
SELECT digits(cast(1234.5 as decimal(8, 2)));
create.select(digits(cast(val(1234.5), DECIMAL(8, 2)))).fetch();
结果是
+----------+ | digits | +----------+ | 00123450 | +----------+
方言支持
此示例使用 jOOQ
digits(cast(val(1234.5), DECIMAL(7, 2)))
翻译成以下特定方言的表达式
访问
(replace(space(7 - len(cstr(cdec(abs((cdec(1.2345E3) * 100)))))), ' ', '0') & cstr(cdec(abs((cdec(1.2345E3) * 100)))))
ASE
(replicate( '0', (7 - char_length(CAST(CAST(abs((CAST(1.2345E3 AS decimal(7, 2)) * 100)) AS decimal(7)) AS varchar(7)))) ) || CAST(CAST(abs((CAST(1.2345E3 AS decimal(7, 2)) * 100)) AS decimal(7)) AS varchar(7)))
Aurora MySQL, MariaDB, MemSQL, MySQL
lpad( CAST(CAST(abs((CAST(1.2345E3 AS decimal(7, 2)) * 100)) AS decimal(7)) AS char(7)), 7, '0' )
Aurora Postgres, Databricks, DuckDB, Exasol, Firebird, HSQLDB, Hana, Postgres, Redshift, Teradata, Trino, Vertica, YugabyteDB
lpad( CAST(CAST(abs((CAST(1.2345E3 AS decimal(7, 2)) * 100)) AS decimal(7)) AS varchar(7)), 7, '0' )
BigQuery
lpad( CAST(CAST(abs((CAST(1.2345E3 AS decimal) * 100)) AS decimal) AS string), 7, '0' )
ClickHouse, DB2
digits(CAST(1.2345E3 AS decimal(7, 2)))
CockroachDB
lpad( CAST(CAST(abs((CAST(CAST(1.2345E3 AS double precision) AS decimal(7, 2)) * 100E0)) AS decimal(7)) AS string(7)), 7, '0' )
H2
lpad( CAST(CAST(abs((CAST(CAST(1.2345E3 AS double) AS decimal(7, 2)) * 100)) AS decimal(7)) AS varchar(7)), 7, '0' )
Informix
lpad( CAST(CAST(abs((CAST(1.2345E3 AS decimal(7, 2)) * 100)) AS decimal(7)) AS lvarchar(7)), 7, '0' )
Oracle
lpad( CAST(CAST(abs((CAST(1.2345E3 AS decimal(7, 2)) * 100)) AS decimal(7)) AS varchar2(7)), 7, '0' )
Snowflake
lpad( CAST(CAST(abs((CAST(1.2345E3 AS number(7, 2)) * 100)) AS number(7)) AS varchar(7)), 7, '0' )
SQLDataWarehouse, SQLServer
(replicate( '0', (7 - len(CAST(CAST(abs((CAST(1.2345E3 AS decimal(7, 2)) * CAST(100 AS decimal(3)))) AS decimal(7)) AS varchar(7)))) ) + CAST(CAST(abs((CAST(1.2345E3 AS decimal(7, 2)) * CAST(100 AS decimal(3)))) AS decimal(7)) AS varchar(7)))
SQLite
substr(replace(hex(zeroblob(7)), '00', '0'), 1, 7 - length(CAST(CAST(abs((CAST(1.2345E3 AS decimal(7, 2)) * CAST(100 AS decimal))) AS decimal(7)) AS varchar(7)))) || CAST(CAST(abs((CAST(1.2345E3 AS decimal(7, 2)) * CAST(100 AS decimal))) AS decimal(7)) AS varchar(7))
Sybase
(repeat( '0', (7 - length(CAST(CAST(abs((CAST(1.2345E3 AS decimal(7, 2)) * 100)) AS decimal(7)) AS varchar(7)))) ) || CAST(CAST(abs((CAST(1.2345E3 AS decimal(7, 2)) * 100)) AS decimal(7)) AS varchar(7)))
Derby
/* UNSUPPORTED */
使用 jOOQ 3.21 生成。早期 jOOQ 版本的支持可能有所不同。 在我们的网站上翻译您自己的 SQL
反馈
您对此页面有任何反馈吗？ 我们很乐意听取您的意见！