oracle之replace结合substr的使用
2024-10-11 06:20:42
select * from( SELECT TMM.ORDER_ID, TMM.IMPORT_ID, TMM.TMALL_ORDER_ID, TMM.MEMBER_NAME, TMM.ALIPAY_USER_NAME, TMM.ALIPAY_PAID_FEE, TMM.MESSAGE, TMM.MESSAGE_SOURCE, CASE TMM.MESSAGE_SOURCE WHEN 'app' THEN 'app端' WHEN 'pc' THEN 'PC端' WHEN 'wap' THEN 'wap端' WHEN 'tm' THEN '天猫' END AS MESSAGE_SOURCE_NAME, TMM.RECEIVE_USER, replace(TMM.RECEIVE_FULL_ADDRESS,substr(TMM.RECEIVE_FULL_ADDRESS,round(length(TMM.RECEIVE_FULL_ADDRESS) , ),nvl(#strLength#,)),'*****') RECEIVE_FULL_ADDRESS, replace( TMM.RECEIVE_TEL, substr( TMM.RECEIVE_TEL, , nvl(#strLength#,)), '****') AS RECEIVE_TEL, TMM.ORDER_CREATE_TIME, TMM.GOODS_NO, TMM.GOODS_TOPIC, TMM.GOODS_COUNT, TMM.IS_CALLBACK, DECODE(TMM.IS_CALLBACK,'N','未回访','Y','已回访') as IS_CALLBACK_NAME, TMM.IS_BUY, DECODE(tmm.IS_BUY,'N','未购买','Y','已购买') as IS_BUY_NAME, TMM.NOT_BUY_REASON, TMM.NOT_BUY_REASON_NOTE, TMM.CALLBACK_TIME, TMM.COMMIT_USERID, TMM.CALLBACK_USERID, TMM.USER_TYPE, DECODE(tmm.USER_TYPE,'N','一次购买用户','Y','二次购买用户') as USER_TYPE_NAME, TMM.CUSTOMER_NATURE, CASE TMM.CUSTOMER_NATURE WHEN 'dg' THEN '代购' WHEN 'zy' THEN '自用' WHEN 'yf' THEN '药贩' END AS CUSTOMER_NATURE_NAME, DECODE(tmm.DISTRIBUTION_TYPE,'N','未分配','A','系统自动分配','H','手动分配') as DISTRIBUTION_TYPE_NAME, TMM.DISTRIBUTION_TYPE, (select user_name from fecerp.sys_user where user_id = tmm.SERVICE_NUM and is_delete='N') AS SERVICE_NAME, TMM.MESSAGE_TIME, (select s.code_value from sys_code s where s.code_id=TMM.NOT_BUY_REASON and s.is_delete='N') as NOT_BUY_REASON_NAME, tmm.ASSIGN_NUM, (select user_id from fecerp.sys_user where user_name = TMM.ASSIGN_NUM and is_delete = 'N') as ASSIGN_ID, ALIPAY_ORDER_FEE, ALIPAY_DELIVERY_FEE, ALIPAY_TOTAL_FEE, (select s.code_type_no from sys_code s where s.code_id = NOT_BUY_REASON and s.is_delete = 'N') as CODE_TYPE_NO, NOTE FROM TMALL_TC_MESSAGE TMM where TMM.Is_Delete='N') mai
最新文章
- 使用Maven加载项目有Dubbo框架时出现的常见异常情况
- webform注册和Repeater
- centos 7安装mysql报错-bash: ./scripts/mysql_install_db: /usr/bin/perl: bad interpreter: No such file or directory
- 对于a标签点击之后可以发邮件和打电话的功能实现
- codeforces B. Sereja and Stairs 解题报告
- M面经Prepare: Positive-Negative partitioning preserving order
- linux安全
- 【转】Xcode中的iOS模拟器(iOS Simulator)的介绍和使用心得
- IS脚本学习
- Android RoboGuice 使用指南
- Spring事务源码阅读笔记
- JavaScript(第十九天)【DOM进阶】
- 在linux和windows用c++编写c接口的动态库
- Python常用的软件包
- redis持久化 (rdb
- iOS SQLite 数据库迁移
- 使用原子类或synchronized(没用Lock)解决阐述多线程所遇到线程安全问题和解决方案
- List集合中的对象按照某个字段去重实现
- easyui combobox 动态加载的两种方法
- 常用开放api【长期更新】