SQL小记之number转varchar2

最新推荐文章于 2024-07-28 14:18:04 发布

山与先生

最新推荐文章于 2024-07-28 14:18:04 发布

阅读量5.6k

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/u010257584/article/details/51322940

本文介绍了一种在Oracle数据库中将number类型的数据转换为varchar2类型的方法，并通过具体实例展示了如何进行表间数据迁移。

某天工作中遇到需求要将某用户的A表的2个number(20,10)的字段数据导入到B表对应的2个varchar2字段中，而2表的关联字段在A表是varchar2，B表是number类型，折腾后，终于完成任务：

SQL> UPDATE BK_ADDRESS_DISTRICT a  SET (a.lat,a.lng)= (SELECT to_char( b.lat,99999999999.9999999999),
to_char(b.lng,99999999999.9999999999 )FROM  tb_sys_area b 
WHERE a.districtcode=to_char(b.areano));

PS：Oracle的相关函数使用需加强，书到用时方恨少啊！

确定要放弃本次机会？

福利倒计时

: :

立减 ¥

普通VIP年卡可用

立即使用

山与先生

关注关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
分享

复制链接

分享到 QQ

分享到新浪微博

扫一扫
举报

举报

MS SQL Server2008大数、小数转varchar

JoeBlackZQQ的专栏

09-29

638

试了下str, cast和convert，发现对于小数或大数，多少都存在一些问题，最后经过尝试终于找到一种满意的答案： select cast(HTJE as decimal(20,2)) from T_HTGL where ID = 1002993 对于金额部分，这里设置了小数最多2位，能满足需求，尾部的0会自动去除，搞定！ ...

Oracle Database ：Oracle11g SQL开发指南学习笔记之（2）：使用SQLPLUS

忘忧草

05-29

1516

1、SQLPLUS常用的编辑命令：㈠、A【ppend】 text：将text附加到当前行之后； SQL> 1 1* select * from emp SQL> a where deptno=20; 1* select * from emp where deptno=20 SQL> append and comm = 500; 1* select

参与评论您还未登录，请先登录后发表或查看评论

更改字段类型-将Number修改成varchar2类型

飘一笑的JAVA

12-22

1万+

实际开发中，添加字段，更改字段类型是常有的事，最近碰到的是数据库中有一个字段ABWORKBYRARATE ，类型为Number(10,5)，需要改成字符串类型varchar2(30)，而且表中已经有很多数据了，直接改报错，改不了。解决方案如下： 1. 重命名原字段 alter table CDS_IMPORT_FORWARDCHARGE rename column ABWORKBYRARA

修改有数据oracle字段类型从number转为varchar

weixin_33998125的博客

10-30

1391

--修改有数据oracle字段类型从number转为varchar--例：修改ta_sp_org_invoice表中RESCUE_PHONE字段类型，从number转为varchar --step1：增加一个原类型缓冲区字段alter table ta_sp_org_invoice add permile_temp number;--step2：将要修改的字段的值存入缓冲区，同时将要修改的字段...

将已有数据的表字段类型由NUMBER修改为VARCHAR2

a327784689的专栏

10-11

1138

alter table pc_qa_sample_result add permile_temp number(10,4); update pc_qa_sample_result set permile_temp = background_value,background_value=null; alter table pc_qa_sample_result modify background_...

Oracle number 类型转换为 varchar2

热门推荐

aaronpan21的专栏

08-10

1万+

项目初期表结构设计是非常重要，在字段类型定义样也要格外小心，业务开展后，修改字段类型代价非常大。本文主要记录在 oracle 中 number 类型转换为 varchar2 数据类型方法以及案例演示。 number类型的数据直接存人 varchar2 类型的字段中，会出现格式问题，如：.5, 5. 等 to_char(number) 可将 number 类型转换为 varchar2 类型，可以指定格式 fmt（可选）；参数n，可以是 NUMBER、BINARY_FLOAT 或 BINARY_DOUBL

我的达梦DCA培训小记

hfxwonderful2019的博客

08-10

1083

@达梦我的达梦DCA培训笔记感谢达梦大学给予我三天时间的培训。新冠疫情期间，不能到现场开展面对面的学习，单依然无法阻挡我们学习达梦数据库基础运维的热情。感谢我们的王文欢老师、李梦等老师的辛勤付出。达梦数据库培训（DCA） 2020年8月3日-8月5日 47931966 / whdbalan DCA 入门级别单机数据库的行情：关系型 Oracle 11G，12C（12.1，12.2），18C（12.2.0.2）机器学习，安全，CDB、PDB MySQL 5.6 5.7 5.8 互联网 SUN

生产系统-oracle调优之索引小例：5s到0.84

深蓝@黄炎龙

06-11

2034

原创作品，出自 “深蓝的blog” 博客，欢迎转载，转载时请务必注明出处，否则有权追究版权法律责任。深蓝的blog：应用系统调优过程小记——索引声明：本文不涉及任何企业业务逻辑、业务框架及行业内技术细节，只涉及到部分开发字段名称保留，只是单纯的围绕oracle做一次技术讨论、分享，不会泄露任何企业核心机密，特此说明。背景介绍：接到客户反馈现象：某市某信

【Sqlite3】【Nodejs 】

weixin_42910765的博客

08-09

1106

serialize可保证回调函数中的代码顺序执行，但其中的代码若是异步调用，其不能将异步调用变为同步调用！执行的顺序是：00000==>22222==>44444==>11111=>33333。上面是封装的sqlite 这里就可以使用了。最近在使用 Sqlite 小记一下。......

旅行社管理系统

来自一名程序猿的案例文档分享

07-28

524

Key WordsBootstrap目录摘要Abstract引言1概述1.1本项目背景介绍1.2目的和意义1.3本文组织结构2开发平台和相关技术介绍2.1开发平台2.1.1开发平台2.1.2运行环境2.2Bootstrap2.2.1Bootstrap介绍2.2.2栅格布局2.2.2轮播2.3SSM2.3.1Spring2.3.2SpringMVC2.3.3Mybatis2.3.4项目分层介绍2.4PageHelper分页插件3。

mysql varchar 转成bigint_MySQL 指南之 SQL 语句基础

weixin_39540744的博客

12-05

3504

个人所有文章整理在此篇，将陆续更新收录:知无涯，行者之路莫言终(我的编程之路)零、结构化查询语言：SQL(Structured Query Language)DDL 数据定义语言管理库，表DML 数据操作语言增删改查 DCL 数据控制语言数据控制，权限访问等准备活动：创建库和表CREATE DATABASE datatype;USE datatype;CREATE TABLE ty...

关于oracle存储过程数据落库测试-number格式转换维varchar格式

金百万的测试博客

01-25

676

如果有结果数据落库，需要将number格式转换维varchar格式，注意存储过程的处理，仅有小数位的时候处理是否符合语气。to_char(0.001 * 100, 'FM990.00') -> 0.1 符合预期。2）0代表如果存在数字则显示数字，不存在则显示0，即占位符。1）9代表如果存在数字则使用数字，不存在显示空格。3）FM代表如果是因为9代表的空格则删除。

numeric转换varchar_SQL怎么样把numeric类型的数据转换为varchar类型的数据

weixin_39548740的博客

12-21

6431

展开全部可以用cast来转换。如：列e69da5e6ba9062616964757a686964616f31333433626465名叫grade，表名为A select cast(grade as numeric(y,x)) from A 其中y代表数据长度，x代表小数位长度。注意：grade中必须全为数字或小数点，如果存在中文或英文，则运行时会报错。使用CAST函数进行数据类型转换时，在下列情...

PL/SQL设置NUMBER显示为字符串

seulww的专栏

04-30

1842

PL/SQL设置NUMBER显示为字符串 2014年4月30日16:51:03 PL/SQL

oracle中number和varchar2的自动转换的情况

一个逻辑混乱的猴子

03-10

1万+

（转）今天测试分区索引，发现oracle智能化的一方面：若列为number类型，用其作为where条件时，变量为varchar2类型时会自动将变量转换为number类型，而不是将列转换为number类型。例如，表testn存在number类型的列a，使用该列做为查询条件时：若变量为varchar2类型，且可合法地转换成number类型，则自动将变量转换为number类型，而不会转换a列

抽取、清洗varchar2到number类型数据的方法（由繁到简）

深蓝@黄炎龙

05-28

2622

原创作品，出自 “深蓝的blog” 博客，欢迎转载，转载时请务必注明出处，否则追究版权法律责任。深蓝的blog：【背景】进行数据抽取时，抽取“联系电话”字段时，发现存在部分中文、英文，需要对该字段进行清理。【垃圾数据存在原因】如果有“联系电话”这样的字段，在设计之初就设置为number，而不是设置成varchar2，再在前端软件做一个约束，相信就不会有这样存在英文或

sql语法，如何将一个数字转成字符串

期待

04-15

2793

1 问题：在插入数据库时，oracle报错，说插入“无效字符” 仔细检查了一下才发现原来是使用merger语句的时候，将一个表中的number类型的字段插入另一个表的varchar的类型的字段中。 2 解决：于是我做了一个转换，将number类型的转成verchar类型的 merge into zt_test tt1 using ip tt on (tt1.info_...

将数值类型（number）转换成日期类型（date）或字符类型（VARCHAR2）

Shawn的ADF空间

08-10

9367

关键字：数值，日期，字符，number，date，VARCHAR2，varchar2，类型转换，oracle，plsql 在Oracle数据库中，有时要处理数值转换为日期类型的数据。首先，要确定Oracle数据库设定的系统时区，例如我们会用东8区。或者，当你能确定（12

ORACLE number类型转换

jsjhegang的博客

07-13

5494

oralce number类型转换

Oracle 把varchar2转成number

最新发布

06-20

<think>我们正在处理用户关于Oracle数据库中将varchar2类型转换为number类型的问题。根据用户的问题，我们需要提供转换方法或解决方案。同时，我们需要参考提供的引用内容，但要注意这些引用可能不是直接相关的，我们需要从中提取有用信息。从引用中我们可以注意到：-引用[1]主要讨论varchar2的最大长度限制，与转换无关。-引用[2]提到了将varchar2存入blob以及blob转成varchar2，不直接相关。-引用[3]讨论了TO_NUMBER()函数的用法，并提到在转换时可能会遇到非法数据的问题，建议在SQL中加上长度校验或其他条件来处理非法数据。-引用[4]则涉及binary_double到number和varchar2的转换，提到了使用ROUND()和LOG()函数处理精度问题，但这不是直接针对varchar2转number。因此，最相关的引用是引用[3]，它提到了TO_NUMBER()函数的使用以及处理非法数据的方法。解决方案：在Oracle中，将varchar2转换为number通常使用TO_NUMBER函数。但是，如果varchar2列中包含非数字字符，转换会抛出错误。因此，我们需要考虑数据清洗或使用其他方法避免错误。方法1：直接使用TO_NUMBER函数```sqlSELECTTO_NUMBER(varchar_column)FROMtable_name;```但这种方法在遇到非数字字符时会报错。方法2：使用正则表达式函数检查是否为数字我们可以先筛选出合法的数字字符串，然后再转换。例如：```sqlSELECTTO_NUMBER(varchar_column)FROMtable_nameWHEREREGEXP_LIKE(varchar_column,'^[+-]?[0-9]*\.?[0-9]+$');```这个正则表达式匹配整数或浮点数（可选正负号）。方法3：使用自定义函数如果数据中非数字的情况复杂，可以创建一个函数来安全转换，例如：```sqlCREATEORREPLACEFUNCTIONsafe_to_number(p_strINVARCHAR2)RETURNNUMBERISv_numNUMBER;BEGINv_num:=TO_NUMBER(p_str);RETURNv_num;EXCEPTIONWHENOTHERSTHENRETURNNULL;--或者根据需求返回一个默认值END;/```然后使用：```sqlSELECTsafe_to_number(varchar_column)FROMtable_name;```方法4：在转换前清理数据（使用TRANSLATE或REPLACE）如果数据中只有特定的非数字字符（如逗号、货币符号等），可以在转换前去除：```sqlSELECTTO_NUMBER(REPLACE(REPLACE(varchar_column,',',''),'$',''))FROMtable_name;```注意事项：-转换前要确保数据长度在number类型的范围内。-注意小数点和千位分隔符的处理。-考虑科学计数法表示的数字，如果存在则需要特殊处理。根据引用[3]的建议，我们还可以在转换时增加条件来过滤掉非法数据，比如长度条件（如长度不超过一定值）或者使用CASE语句结合正则表达式。例如：```sqlSELECTCASEWHENREGEXP_LIKE(varchar_column,'^[+-]?[0-9]*\.?[0-9]+$')THENTO_NUMBER(varchar_column)ELSENULLENDFROMtable_name;```总结：最常用的方法是TO_NUMBER函数，但必须确保字符串是有效的数字表示。如果数据质量不可控，建议先清洗数据或使用安全转换方法。相关问题：1.在Oracle中，如何将字符串转换为日期？2.在Oracle中，TO_NUMBER转换失败时如何避免程序报错？3.Oracle中除了TO_NUMBER，还有哪些方法可以将字符串转换为数字？4.如何批量将varchar2列的数据类型更改为number类型？</think>### Oracle varchar2 转 number 的解决方案在 Oracle 数据库中，将 `varchar2` 类型转换为 `number` 类型主要有以下方法： #### 1. **直接使用 TO_NUMBER() 函数** ```sql SELECT TO_NUMBER(varchar_column) AS converted_value FROM your_table; ``` **注意**：如果列包含非数字字符（如字母、符号），会抛出 `ORA-01722: invalid number` 错误[^3]。 #### 2. **安全转换（处理非法数据）** ```sql SELECT CASE WHEN REGEXP_LIKE(varchar_column, '^[-+]?\d+(\.\d*)?$') THEN TO_NUMBER(varchar_column) ELSE NULL -- 或指定默认值 END AS safe_value FROM your_table; ``` **说明**： - 使用正则表达式 `REGEXP_LIKE` 过滤纯数字字符串 - 支持正负号和小数点 - 非法数据返回 `NULL` 或自定义值[^3] #### 3. **数据清洗后转换** ```sql SELECT TO_NUMBER( TRANSLATE(varchar_column, '$,ABCDEFGHIJKLMNOPQRSTUVWXYZ', ' ') -- 移除字母和特殊符号 ) AS cleaned_value FROM your_table; ``` **适用场景**：数据包含货币符号（`$`）或千位分隔符（`,`）[^3] #### 4. **自定义异常处理函数** ```sql CREATE OR REPLACE FUNCTION safe_to_num(p_str VARCHAR2) RETURN NUMBER IS BEGIN RETURN TO_NUMBER(p_str); EXCEPTION WHEN VALUE_ERROR THEN RETURN NULL; -- 捕获转换错误 END; / SELECT safe_to_num(varchar_column) FROM your_table; ``` **优势**：避免 SQL 中断，批量处理时更安全[^3] #### 5. **修改表结构（永久转换）** ```sql ALTER TABLE your_table ADD (temp_num NUMBER); UPDATE your_table SET temp_num = TO_NUMBER(varchar_column) WHERE REGEXP_LIKE(varchar_column, '^\d+$'); -- 仅转换合法数字 ALTER TABLE your_table DROP COLUMN varchar_column; ALTER TABLE your_table RENAME COLUMN temp_num TO varchar_column; ``` **注意**：需提前验证数据兼容性，避免丢失数据[^1] --- ### 关键注意事项 1. **长度限制**： - `varchar2` 最大支持 4000 字节（SQL 环境）[^1] - 转换前需确保数字字符串不超过 `number` 类型的精度范围 2. **性能优化**： ```sql WHERE LENGTH(varchar_column) <= 38 -- number最大精度38位 AND TRANSLATE(varchar_column, 'x0123456789.', 'x') IS NULL -- 快速过滤非数字 ``` 3. **科学计数法处理**： ```sql SELECT TO_NUMBER('1.23E+5') FROM dual; -- 支持 123000 ``` 4. **空值处理**： ```sql SELECT TO_NUMBER(NULLIF(trim(varchar_column), '')) FROM ... ``` --- ### 常见错误解决方案 | 错误场景 | 解决方案 | |----------|----------| | `ORA-01722`（无效数字） | 使用 `CASE` 或正则表达式预过滤 | | 前导/尾随空格 | 先用 `TRIM()` 处理字符串 | | 小数点格式冲突 | 用 `ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,'` 指定格式 | | 性能问题 | 创建函数索引：`CREATE INDEX idx_num ON tab(safe_to_num(varchar_col))` |