英文:
How to add the colon(:) inbetween numbers in oracle sql table?
问题
预期输出：
12:34:56
英文:
Actual output:
123456
Expected output:
12:34:56
答案1
得分: 2
试试正则表达式，比如
SQL> 从双重选择中选择rtrim(regexp_replace('123456'，'（\ d {2}）'，'\ 1：')，'：')结果
2来自二重;
结果
12:34:56
SQL>;
英文:
Or, try regular expression, such as
SQL> select rtrim(regexp_replace('123456', '(\d{2})', ':'), ':') result
2 from dual;
RESULT
--------
12:34:56
SQL>
答案2
得分: 0
你可以使用 SUBSTR() 来切割数字，然后使用 || 连接它们在一起。
选择 SUBSTR(nbr,1,2)||':'||SUBSTR(nbr,3,2)||':'||SUBSTR(nbr,5,2) 作为结果
从 (select 123456 nbr from dual)
输出：
| RESULT |
|---|
| 12:34:56 |
英文:
Query:
You can use SUBSTR() to slice the number and || to concate those togather.
select SUBSTR(nbr,1,2)||':'||SUBSTR(nbr,3,2)||':'||SUBSTR(nbr,5,2) result from
(select 123456 nbr from dual)
Output:
| RESULT |
|---|
| 12:34:56 |
答案3
得分: 0
您已经展示了数字123456，并希望将其格式化为12:34:56。因此，您想要从一个数字转换为一个字符串。由于'12:34:56'不是数字123456的表示方式（例如'123456'或'123,456'或'123456.0'），您需要进行两个步骤：
- 将数字123456转换为字符串'123456'。
- 使用字符串操作函数将字符串'123456'转换为'12:34:56'。
我不知道您处理的是什么数字。它可以有八位数字，您想要将其分成两组吗？它可以有七位数字吗？如果是这样，您要如何格式化它们？
在下面的示例中，我假设数字是一到六位数，我们将创建前导零。例如，0变为'00:00:00'。
select regexp_replace(to_char(num, 'fm000000'), '^(\d\d)(\d\d)(\d\d)$', '::')
from mytable;
我在这里使用REGEXP_REPLACE，因为我更喜欢正则表达式来执行这样的任务，因为它们更简洁。
英文:
You have shown the number 123456 and want it formatted as 12:34:56. So you want to get from a number to a string. As '12:34:56' is not a representation of the number 123456 (like '123456' or '123,456' or '123456.0' would be), you need two steps:
- Convert the number 123456 to a string '123456'.
- Use string manipulation functions to convert the string '123456' to '12:34:56'.
I don't know what numbers you are dealing with. Can it have eight digits and you want it separated into groups of two? Can it have seven digits? If so, how to format them?
In below example I assume one to six digits, where we'll create leading zeros. E.g. 0 becomes '00:00:00'.
select regexp_replace(to_char(num, 'fm000000'), '^(\d\d)(\d\d)(\d\d)$', '::')
from mytable;
I am using REGEXP_REPLACE here, because I prefer regular expressions for such tasks for their conciseness.
答案4
得分: 0
如果您有时间，那么将数字转换为DATE，然后将其格式化为字符串：
SELECT TO_CHAR(TO_DATE(123456, 'HH24MISS'), 'HH24:MI:SS') AS time
FROM DUAL;
输出结果为：
| TIME |
|---|
| 12:34:56 |
英文:
If you have a time then convert the number to a DATE and then format it as a string:
SELECT TO_CHAR(TO_DATE(123456, 'HH24MISS'), 'HH24:MI:SS') AS time
FROM DUAL;
Which outputs:
| TIME |
|---|
| 12:34:56 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论