DB2 SQL 将十进制转换为带填充零的字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15535260/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
DB2 SQL Convert Decimal to Character with Padded Zeros
提问by macunte
How can I convert my DECIMAL(11)field from 12345678to a character value of 00012345678?
如何将我的DECIMAL(11)字段从12345678转换为字符值00012345678？
采纳答案by Adam111p
My LeftPad function without LeftPad function
我的 LeftPad 功能没有 LeftPad 功能
REPEAT('0', 4-length(MY_COLUMN_VALUE))||CHAR(MY_COLUMN_VALUE) as NEW_COLUMN
MY_COLUMN_VALUE NEW_COLUMN
1 0004
23 0023
testing ...
测试...
SELECT '32' MY_VALUE, REPEAT('0', 4-length('23'))||CHAR('23') as LEFTPAB_MY_VALUE FROM sysibm.sysdummy1
回答by kelgwiin
Only use the DIGITSfunction, because this verifies the length of the field numeric or decimal, etc and completes with zeros to the left when is necessary.
仅使用该 DIGITS函数，因为这将验证数字或小数等字段的长度，并在必要时在左侧补全零。
SELECT DIGITS(FIELD) FROM ...
The length of the resulting string is always:
结果字符串的长度始终为：
- 5 if the argument is a small integer
- 10 if the argument is a large integer
- 19 if the argument is a big integer
- 5 如果参数是一个小整数
- 10 如果参数是一个大整数
- 19 如果参数是一个大整数
回答by bhamby
Based on your comment in @Mr Fuzzy Botton's answer, I'm guessing you're on DB2 for i, which does not have the LPADfunction. You could instead use a combination of the REPEATand RIGHTfunctions:
根据你在@Mr Fuzzy Botton 的回答中的评论，我猜你在DB2 for i，它没有这个LPAD功能。您可以改为使用REPEAT和RIGHT函数的组合：
SELECT RIGHT(REPEAT('0', 11) || LTRIM(CHAR(your_field)), 11)
FROM your_table
回答by AjV Jsy
Using http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2.doc.sqlref/castsp.htmfor details on CAST
and http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z10.doc.sqlref/src/tpc/db2z_scalarfunctionsintro.htmfor string functions,
I assume this should do the trick -
使用http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2.doc.sqlref/castsp.htm了解 CAST
和http://pic.dhe.ibm.com 的详细信息/infocenter/dzichelp/v2r2/topic/com.ibm.db2z10.doc.sqlref/src/tpc/db2z_scalarfunctionsintro.htm对于字符串函数，
我认为这应该可以解决问题 -
SELECT LPAD( CAST(FIELD AS CHAR(11)) ,11,'0') AS PADDEDFIELD
SELECT LPAD( CAST(FIELD AS CHAR(11)) ,11,'0') AS PADDEDFIELD
回答by Filipe
Don't know if you've worked it out, however try this:
不知道你是否已经解决了，但是试试这个：
SELECT LPAD( DIGITS( fieldName ), 11, '0') ) as paddedFieldName FROM yourTable
The LPAD is the left padding, but the DIGITS function was the only way I got DB2 to treat the numeric value like a string.
LPAD 是左填充，但 DIGITS 函数是我让 DB2 将数值视为字符串的唯一方法。
回答by user2338816
If this is DB2 for i, and myColumn data type is DECIMAL with precision (11) and scale (0), then:
如果这是 DB2 for i，并且 myColumn 数据类型是精度 (11) 和小数位数 (0) 的 DECIMAL，则：
SELECT digits( myColumn ) FROM sysibm.sysdummy1
will return:
将返回：
....+....1.
DIGITS
00001234567
Changing the number of leading zeros could be done in many ways. CASTing to a different precision before using DIGITS() is one way.
可以通过多种方式更改前导零的数量。在使用 DIGITS() 之前转换为不同的精度是一种方法。
回答by macunte
I just went the other direction: cast(field as int)
我只是去了另一个方向：cast(field as int)
回答by funnyfish
Try this for your field x:
为您的字段 x 试试这个：
substr(digits(x), 33 - length(x), length(x) )
回答by user10629183
SELECT SUBSTRING(
CHAR(100000000000+fieldName),
2,11
) as paddedFieldName
FROM yourTable
I only wanted to define my field once in the select statement so the above worked for me and is tidy
我只想在 select 语句中定义一次我的字段，因此上述内容对我有用并且很整洁
回答by Not an expert
From Numeric 8,0 (datenumfld=20170101) to 01/01/2017This works for me:
从数字 8,0 (datenumfld=20170101) 到01/01/2017这对我有用：
DATE(TO_DATE(CHAR(datenumfld), 'YYYYMMDD')) as YourDate
