Oracle 【to_number】【instr】
需求:对一个包含数字的字符串排序
search后参考了 http://www.cnblogs.com/m-cnblogs/archive/2012/03/30/2425938.html
截屏:
(用到了to_number(str,split 1,...) instr() replace() substr())
TO_NUMBER(x [, format], [ nls_language ])
converts x to a NUMBER
.
- x is the string that will be converted to a number.
- format, optional, is the format that will be used to convert x to a number.
- nls_language, optional, is the nls language used to convert x to a number
INSTR(string, pattern-to-find)
INSTR
returns the location (beginning) of a pattern in a given string. Its simple form is:
The general syntax of INSTR is:
INSTR (string to search, search pattern [, start [,occurrence]])
The arguments within brackets ([]) are optional.
在一个字符串中查找指定的字符,返回被查找到的指定的字符的位置。
如果start的值为负数,则代表从右往左进行查找,但是位置数据仍然从左向右计算。
SUBSTR
retrieves a portion of the string. The general format for this function is:
SUBSTR(string, start_at_position[, number_of_characters_to_retrieve])
从给定的字符表达式返回一个子字符串。
REPLACE(source-string, pattern-to-find, pattern-to-replace-by)
(to)‘2011-10-11’ ‘2011/10/11’ 转换
update 表1 t set t.列1=replace((select 列1from 表1 a where a.主键列=t.主键列) , '/' , '-' )
例:select replace ('111222333444','222','888') from dual;
输出为 '111888333444'
最后我的代码
pn
to_number(str,’-’)
str:
replace(source ,’-’)
source:
substr(pn,instr(pn,’-’)+1,
length(pn)-1-instr(pn,’-’))
最新文章
- CRM 2013 相关下载 / 2013-10-11
- 分分钟教你从根本上认识Struts2框架
- 6个关于dd命令备份Linux系统的例子
- SpringMVC、Struts1、Struts2和SSH2框架中单例与多例的解析
- 【NOIP模拟赛】lover——心上人
- C# 获取当前星期几三种实现方法(转)
- ZSDRM001-发货清单
- winform下mapxtreme2008 v7.0 生成release版提示找不到dll问题
- Codeforces Round #154 (Div. 2) : B
- C++ deepin
- HDU 3030 - Increasing Speed Limits
- webpack配置报错:invalid configuration object.webpack has been initialisted using a configuration objcet that does not match thie API schema
- java并发之CyclicBarrier
- 使用Phalcon框架开发一个简易的博客系统
- vsftp为不同用户设置不同的ftp的根目录
- WINDOWS自带md5校验工具
- embedding与word2vec
- Javascript Property Names
- lucene查询索引之QueryParser解析查询——(八)
- 错误:Parameter '0' not found.Available parameters are [arg1, arg0, param1, param2]的解决方法