Oracle中判断字段是否为数字篮球世界杯赌球
在我们平常的开发中可能会遇到这样的问题，就是判断某一列是否全部由数字组成，我们都知道oracle并没有给我们提供这样一个现成的函数，那么根据我的经验我总结了两个行之有效的方法：
【**函数】Oracle**函数系列（1）--字符函数
1.使用trim translate函数：
复制代码 代码如下:select * from table where trim(translate(column,'0123456789',' ')) is NULL;
1 BLOG文档结构图
这里要注意的是：translate函数的第三个参数是一个空格，不是'', 因为translate的第三个参数如果为空的话，那么永远返回'',这样的就不能达到过滤纯数字的目的。这样把所有的数字都转化为空格，如果全部是由数 构成，那么一旦trim后自然是空，实现了上述目标。当然如果想排除空项的话，可以这样写：
2 前言部分
复制代码 代码如下:select * from table where trim(translate(nvl(column,'x'),'0123456789',' ')) is NULL;--x 表示任何'0-9'以外的字符。
2.1 导读和注意事项
各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~O(∩_∩)O~：
① 常见字符函数（lower、upper、initcap、concat、substr、length、intr、lpad、rpad、trim、chr、ascii、replace、translate）的使用
② 判断字符串中是否含有汉字
③ substr和instr的联合使用
④ 替换函数的使用
Tips：
① 本文在itpub（http://blog.itpub.net/26736162）、博客园(http://www.cnblogs.com/lhrbest)和微信公众号（xiaomaimiaolhr）上有同步更新。
② 文章中用到的所有代码、相关软件、相关资料及本文的pdf版本都请前往小麦苗的云盘下载，小麦苗的云盘地址见：http://blog.itpub.net/26736162/viewspace-1624453/。
③ 若网页文章代码格式有错乱，请尝试以下办法：①去博客园地址阅读，②下载pdf格式的文档来阅读。
④ 在本篇BLOG中，代码输出部分一般放在一行一列的表格中。其中，需要特别关注的地方我都用灰色背景和粉红色字体来表示，比如在下边的例子中，thread 1的最大归档日志号为33，thread 2的最大归档日志号为43是需要特别关注的地方；而命令一般使用黄色背景和红色字体标注；对代码或代码输出部分的注释一般采用蓝色字体表示。
List of Archived Logs in backup set 11 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- ------------------- ---------- --------- 1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48 1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58 2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49 2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53 [ZHLHRDB1:root]:/>lsvg -o T_XLHRD_APP1_vg rootvg [ZHLHRDB1:root]:/> 00:27:22 SQL> alter tablespace idxtbs read write; ====》2097152*512/1024/1024/1024=1G |
本文如有错误或不完善的地方请大家多多指正，ITPUB留言或QQ皆可，您的批评指正是我写作的最大动力。
2.使用regexp_like函数：
2.2 相关文章链接
http://blog.itpub.net/26736162/viewspace-1688209/ |
|
复制代码 代码如下:select * from table where regexp_like(column,'^[0-9] [0-9]$');
2.3 本文简介
最近因为360云盘停止个人用户服务的事情要忙，所以等待事件系列就不更新了，转而更新函数系列吧，毕竟函数比较简单嘛。
本篇文章为第一篇，讲解所有的字符函数。
先来一道考题：
Which four are types of functions available in SQL? (Choose 4)
A. string
B. character
C. integer
D. calendar
E. numeric
F. translation
G. date
H. conversion
Answer: B,E,G,H
这里要注意的是：regexp_like函数不是在所有的oracle版本中 都能使用的。regexp_like是oracle支持正则表达式的四个函数：regexp_like，regexp_replace，regexp_instr,regexp_substr中的 一个，有关这方面更加详细信息，请关注相关文档。
3 字符函数
字符函数是 oracle 中最常用的函数，我们来看看有哪些字符函数：
（一）大小写控制函数
l lower(char)：将字符串全部转化为小写的格式。
l upper(char)：将字符串全部转化为大写的格式。
l initcap('SQL course') 每个单词的首字母大写，其余变为小写，结果：Sql Course，
（二）字符控制函数
l concat('Hello','World') 字符串连接，结果:HelloWorld
l length(char)：返回字符串的长度。
l substr(char,m,n)：取字符串的子串，m表示起点，n 代表取 n 个字符的意思
l replace(char1,search_string,replace_string) 替换
l instr(char1,char2,[,n[,m]]) 取子串在字符串的位置(特别取某一个特殊字符在原字符串中的位置)
l trim(' Hello World '), 结果为： “Hello World”
l ltrim(' Hello World ')，结果为： “Hello World ”
l rtrim(' Hello World ')，结果为： “ Hello World”
l lpad(salary,10,'*') 结果：*****24000
l rpad(salary, 10, '*') 结果：24000*****
l chr()函数将ASCII码转换为字符： ASCII码 –》 字符；
l ascii()函数将字符转换为ASCII码：字符 –》 ASCII码；
总结一下，如果oracle支持regexp_like函数，推荐使用正则的方式，如果不支持则用trim translate这种方式。
3.0.1 lower: **将字符串全部转化**为小写的格式
SQL> select lower('Hello WORLD') from dual;
LOWER('HELL ----------- hello world
SQL> Select ENAME,LOWER(ENAME) From SCOTT.EMP;
ENAME LOWER(ENAM ---------- ---------- SMITH smith ALLEN allen WARD ward JONES jones MARTIN martin BLAKE blake CLARK clark SCOTT scott KING king TURNER turner ADAMS adams JAMES james FORD ford MILLER miller |
3.0.2 upper: **将字符串全部转化为大**写的格式
经常用于比较字符串的内容：
SQL> select upper('Hello WORLD') from dual;
UPPER('HELL ----------- HELLO WORLD
SQL> select ename,sal,hiredate from scott.emp where lower(ename) = 'ward';
ENAME SAL HIREDATE ---------- ---------- ------------------- WARD 1250 1981-02-22 00:00:00 |
3.0.3 initcap**:每个字符串的首字母大写，其余变为小写**
select initcap('SQL coURse ') from dual;
结果：
3.0.4 concat:连接两个不同的列，concat只能接受两个参数
SQL> Select CONCAT(EMPNO,ENAME) From SCOTT.EMP;
CONCAT(EMPNO,ENAME) -------------------------------------------------- 7369SMITH 7499ALLEN 7521WARD 7566JONES 7654MARTIN 7698BLAKE 7782CLARK 7788SCOTT 7839KING 7844TURNER 7876ADAMS 7900JAMES 7902FORD 7934MILLER
14 rows selected.
SQL> Select CONCAT('A','B') From dual;
CO -- AB
SQL> select concat(concat('A','B'),'c') from dual;--可以嵌套使用
CON --- ABc |
可以使用连字符 || 来连接多列：
SELECT 'A'||'B' FROM dual;
3.0.5 substr:截取子串
下标从1开始，这个函数有三个参数，允许你将目标字符串的一部份输出，第一个参数为目标字符串，第二个参数是将要输出的子串的起点，第三个参数是将要输出的子串的长度，如果没有第三个参数，则余下的字符全部输出。
注意：如果第二个参数为负数 那么将会从目标字符串的尾部开始向前定位至负数的绝对值的位置
Select SUBSTR(ENAME,2) From EMP--从第2个位置截到末尾
Select SUBSTR(ENAME,2,3) From EMP--从第2个位置截取3个字母
SELECT SUBSTR('lihuarong',0,3) col_1**,**
SUBSTR('lihuarong',1,3)col_2**,**
SUBSTR('lihuarong',3)col_3**,**
SUBSTR('lihuarong',-4,3) col_4
FROM DUAL;
SYS@raclhr1> SELECT SUBSTR('lihuarong',0,3) col_1, 2 SUBSTR('lihuarong',1,3)col_2, 3 SUBSTR('lihuarong',3)col_3, 4 SUBSTR('lihuarong',-4,3) col_4 5 FROM DUAL;
COL COL COL_3 COL --- --- ------- --- lih lih huarong ron |
注意： 如果第二个参数大于第一个参数的长度，则第一个参数从该列默认的长度开始计算。如下例的name列默认的长度为10
select name,substr(name,-4,3**),**
substr(name,-5,3)
from dual;
3.0.6 length:求字符串长度
SELECT length('重庆贵阳30N重庆贵阳30N-77623') FROM dual;
SELECT length(77623) FROM dual;
SELECT length(sysdate),sysdate FROM dual;
SELECT length(1/2) FROM dual;
SELECT length('1/2'),
length(1 / 2**),**
1 / 2**,**
length(0.5**)**
FROM dual;
一、 Oracle中 length 和 lengthb 区别** **
The LENGTH functions return the length of char. LENGTH calculates length usingcharacters as defined by the input character set. --返回以字符为单位的长度.
LENGTHB usesbytes instead of characters. --返回以字节为单位的长度.
LENGTHC usesUnicode complete characters. --返回以Unicode完全字符为单位的长度.
LENGTH2 usesUCS2 code points.--返回以UCS2代码点为单位的长度.
LENGTH4 usesUCS4 code points. --返回以UCS4代码点为单位的长度.
char can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The exceptions are LENGTHC, LENGTH2, and LENGTH4, which do not allow char to be a CLOB or NCLOB. The return value is of data type NUMBER. If char has data type CHAR, then the length includes all trailing blanks. If char is null, then this function returns null.
Restriction on LENGTHB The LENGTHB function is supported for single-byte LOBs only. It cannot be used with CLOB and NCLOB data in a multibyte character set.
Examples
The following example uses the LENGTH function using a single-byte database character set:
SELECT LENGTH('CANDIDE') "Length in characters"
FROM DUAL;
Length in characters
7
The next example assumes a double-byte database character set.
SELECT LENGTHB ('CANDIDE') "Length in bytes"
FROM DUAL;
Length in bytes
14
SQL> select length('北京') from dual;
LENGTH('北京')
2
SQL> select lengthb('北京') from dual;
LENGTHB('北京')
6
SQL> select lengthb('BeiJing') from dual;
LENGTHB('BEIJING')
7
SQL> select length('BeiJing') from dual;
LENGTH('BEIJING')
7
值得一提的是：
在不同的数据库，因为字符集的不同，LENGTHB得到的值可能会不一样。如ZHS16GBK采用两个byte位来定义一个汉字。而在UTF8，采用3个byte。
--查看字符集语句
SQL> select * from nls_database_parameters where parameter='NLS_CHARACTERSET';
PARAMETER VALUE
select lengthb('齐'),length('齐') from dual
在计算单字节的字符串时。返回的值是相同的。
select lengthb('string'),length('string') from dual
2 、通过对同一个字符串的长度判断
lengthb(string)=length(string)
可以判断是否含有汉字。
例子
SELECT lengthb('齐'),
length('齐')
FROM dual;
SELECT l.code,
length(l.code),
lengthb(l.code),
length2(l.code),
length4(l.code),
lengthc(l.code)
FROM xb_link l
WHERE length(l.code)篮球世界杯赌球， != lengthb(l.code)
AND length(l.code) < 20**;**
二、 判断字符串中是否含有汉字
参考：
http://blog.itpub.net/26736162/viewspace-1688209/ |
|
3.0.7 instr :相当于String类中的indexOf，求索引
如果需要知道在一个字符串中满足特定的内容的子串位置可以使用INSTR，它的第一个参数是目标字符串， 第二个参数是匹配的内容，第三和第四个参数是数字，用以指定开始搜索的起点以及指出第几个满足条件的将会被返回。
默认第三个与第四个参数的数值均为1，如果第三个数值为负数那么将会从后向前搜索。
如果未匹配到则返回0；
SELECT INSTR('HelloWHoldhH', 'H', 6, 2) l1, --从第6个位置开始，第2次出现H的位置
INSTR('HelloWHoldhH', 'H') l2, --从第1个位置开始，第1次出现H的位置
INSTR('HelloWHoldhH', 'H', 2) l3, --从第2个位置开始，第1次出现H的位置
INSTR('HelloWHoldhH', 'H', -1) l4, --从倒数第1个位置开始从右往左搜索，第1次出现H的位置
INSTR('HelloWHoldhH','/') l5--从第1个位置开始，第1次出现H的位置，如果匹配不到则返回0
FROM DUAL;
SYS@raclhr1> SELECT INSTR('HelloWHoldhH', 'H', 6, 2) l1, --从第6个位置开始，第2次出现H的位置 2 INSTR('HelloWHoldhH', 'H') l2, --从第1个位置开始，第1次出现H的位置 3 INSTR('HelloWHoldhH', 'H', 2) l3, --从第2个位置开始，第1次出现H的位置 4 INSTR('HelloWHoldhH', 'H', -1) l4, --从倒数第1个位置开始从右往左搜索，第1次出现H的位置 5 INSTR('HelloWHoldhH','/') l5--从第1个位置开始，第1次出现H的位置，如果匹配不到则返回0 6 FROM DUAL; L1 L2 L3 L4 L5 ---------- ---------- ---------- ---------- ---------- 12 1 7 12 0 |
注意：无论是从前向后还是从后向前搜索，返回的数值都是从左到右的数值。
