SQL 删除前导零
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9091297/
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
Remove leading zeros
提问by Bob
Given data in a column which look like this:
给定列中的数据，如下所示：
00001 00
00026 00
I need to use SQL to remove anything after the space and all leading zeros from the values so that the final output will be:
我需要使用 SQL 从值中删除空格和所有前导零之后的任何内容，以便最终输出：
1
26
How can I best do this?
我怎样才能最好地做到这一点？
Btw I'm using DB2
顺便说一句，我正在使用 DB2
回答by bhamby
This was tested on DB2 for Linux/Unix/Windows and z/OS.
这已在 DB2 for Linux/Unix/Windows 和 z/OS 上进行了测试。
You can use the LOCATE()function in DB2 to find the character position of the first space in a string, and then send that to SUBSTR()as the end location (minus one) to get only the first number of the string. Casting to INTwill get rid of the leading zeros, but if you need it in string form, you can CASTagain to CHAR.
您可以使用LOCATE()DB2 中的函数查找字符串中第一个空格的字符位置，然后将其发送到SUBSTR()作为结束位置（减一）以仅获取字符串的第一个数字。转换 toINT将去掉前导零，但如果您需要以字符串形式，您可以CAST再次 to CHAR。
SELECT CAST(SUBSTR(col, 1, LOCATE(' ', col) - 1) AS INT)
FROM tab
回答by pilcrow
In DB2 (Express-C 9.7.5) you can use the SQL standard TRIM()function:
在 DB2 (Express-C 9.7.5) 中，您可以使用 SQL 标准TRIM()函数：
db2 => CREATE TABLE tbl (vc VARCHAR(64))
DB20000I The SQL command completed successfully.
db2 => INSERT INTO tbl (vc) VALUES ('00001 00'), ('00026 00')
DB20000I The SQL command completed successfully.
db2 => SELECT TRIM(TRIM('0' FROM vc)) AS trimmed FROM tbl
TRIMMED
----------------------------------------------------------------
1
26
2 record(s) selected.
The inner TRIM()removes leading and trailing zero characters, while the outer trim removes spaces.
内部TRIM()删除前导和尾随零字符，而外部修剪删除空格。
回答by D. Kermott
This worked for me on the AS400 DB2. The "L" stands for Leading. You can also use "T" for Trailing.
这在 AS400 DB2 上对我有用。“L”代表领先。您也可以使用“T”作为尾随。
回答by Cory Dolphin
I am assuming the field type is currently VARCHAR, do you need to store things other than INTs?
我假设当前的字段类型是VARCHAR，您是否需要存储 INT 以外的内容？
If the field type was INT, they would be removed automatically.
如果字段类型是INT，它们将被自动删除。
Alternatively, to select the values:
或者，要选择值：
SELECT (CAST(CAST Col1 AS int) AS varchar) AS Col1
回答by RET
One option is implicit casting: SELECT SUBSTR(column, 1, 5) + 0 AS column_as_number ...
一种选择是隐式转换： SELECT SUBSTR(column, 1, 5) + 0 AS column_as_number ...
That assumes that the structure is nnnnn nn, ie exactly 5 characters, a space and two more characters.
假设结构是nnnnn nn，即正好 5 个字符、一个空格和另外两个字符。
Explicit casting, ie SUBSTR(column,1,5)::INT is also a possibility, but exact syntax depends on the RDBMS in question.
显式转换，即 SUBSTR(column,1,5)::INT 也是一种可能性，但确切的语法取决于所讨论的 RDBMS。
回答by Seph
Use the following to achieve this when the space location is variable, or even when it's fixed and you want to make a more robust query (in case it moves later):
当空间位置是可变的，或者即使它是固定的并且您想要进行更健壮的查询（以防它稍后移动）时，使用以下方法来实现这一点：
SELECT CAST(SUBSTR(LTRIM('00123 45'), 1, CASE WHEN LOCATE(' ', LTRIM('00123 45')) <= 1 THEN LEN('00123 45') ELSE LOCATE(' ', LTRIM('00123 45')) - 1 END) AS BIGINT)
If you know the column will always contain a blank space after the start:
如果您知道该列在开始后将始终包含一个空格：
SELECT CAST(LOCATE(LTRIM('00123 45'), 1, LOCATE(' ', LTRIM('00123 45')) - 1) AS BIGINT)
both of these result in:
这两者都会导致：
123
so your query would
所以你的查询会
SELECT CAST(SUBSTR(LTRIM(myCol1), 1, CASE WHEN LOCATE(' ', LTRIM(myCol1)) <= 1 THEN LEN(myCol1) ELSE LOCATE(' ', LTRIM(myCol1)) - 1 END) AS BIGINT)
FROM myTable1
This removes any content after the first space character (ignoring leading spaces), and then converts the remainder to a 64bit integer which will then remove all leading zeroes.
这将删除第一个空格字符之后的任何内容（忽略前导空格），然后将余数转换为 64 位整数，然后将删除所有前导零。
If you want to keep all the numbers and just remove the leading zeroes and any spaces you can use:
如果您想保留所有数字并删除前导零和您可以使用的任何空格：
SELECT CAST(REPLACE('00123 45', ' ', '') AS BIGINT)
While my answer might seem quite verbose compared to simply SELECT CAST(SUBSTR(myCol1, 1, 5) AS BIGINT) FROM myTable1but it allows for the space character to not always be there, situations where the myCol1value is not of the form nnnnn nnif the string is nn nnthen the convert to intwill fail.
虽然与简单相比，我的答案可能看起来相当冗长，SELECT CAST(SUBSTR(myCol1, 1, 5) AS BIGINT) FROM myTable1但它允许空格字符并不总是存在，如果字符串是然后转换为，则myCol1值不是形式的nnnnn nn情况将失败。nn nnint
Remember to be careful if you use the TRIMfunction to remove the leading zeroes, and actually in all situations you will need to test your code with data like 00120 00and see if it returns 12instead of the correct value of 120.
如果您使用该TRIM函数删除前导零，请记住要小心，实际上在所有情况下，您都需要使用数据来测试您的代码，00120 00看看它是否返回12而不是正确的值120。
回答by bek
I found this thread for some reason and find it odd that no one actually answered the question. It seems that the goal is to return a left adjusted field:
出于某种原因，我发现了这个线程，并发现没有人真正回答这个问题很奇怪。似乎目标是返回一个左调整的字段：
SELECT
TRIM(L '0' FROM SUBSTR(trim(col) || ' ',1,LOCATE(' ',trim(col) || ' ') - 1))
FROM tab
