oracle中的substrb()函数怎么使用??请达人指教!!!
解决方案 »
- 求助:分布式数据库实时双向同步的解决方案或思路
- 求助一个简单的 PL/SQL编成 2
- 求11gR2安装以后,参数配置的问题,数据库采用主从架构,配好后主要面向网站应用,包括论坛、类似网上购物等。
- oracle中有没有类似excel中的and函数可以实现形如:if(a1 and b1,100,50)的表达形式?
- 一道面试题
- ORACLE NVARCHAR2(100)
- 我在Oracle里用SQL创建表,如何像SQL Server一样定义自动编号列(int identity(1,1)),以及定义布尔型列?
- 请教一个游标问题
- SOS:中文乱码数据的得挽救
- PL/SQL Developer13查询无输出
- BI和Oracle EBS比较如何?
- ORA-03113:通信通道的文件结束
例如1234567890
想截取890
select substr('1234567890',8,3) from dual
谢谢,不过你好想看掉了一个b是substrb();不是substr();这两个用法是不一样的.
c1为一字符串,i,j为整数,从c1的第i位开始返回长度为j的子字符串,如果j为空,则直到串的尾部。
select SUBSTR('Message',1,4) from dualSUBSMess SUBSTRB(,<i>[,])
与SUBSTR大致相同,只是I,J是以字节计算。
刚才试了一下,不管源数据是字符还是数字都是相同的结果,不知道why了
在我的机器上是这样的,一个汉字占3个字节
SQL> select substr('你真的好吗',-3) from dual;的好吗SQL> select substrb('你真的好吗',-3) from dual;吗SQL> select substr('abcde',-3) from dual;cdeSQL> select substrb('abcde',-3) from dual;cde
SQL> select substrb('你真的好吗',-2) from dual; 吗
你和toad仔细看一下,其实返回了三个字节" 吗"
只不过"吗"前面的那个字节显示不出来,为空字符
你和toad仔细看一下,其实返回了三个字节" 吗"
只不过"吗"前面的那个字节显示不出来,所以显示为空格字符
substrb一个汉字两个字符不过要看数据本身设置参数如何
要理解数据加中 varchar(4000)就是按b计算的,所以就算是汉字,估计也存不了4000个字汉字
substrb()是按位截取
substrb()是按位截取
substr::=
Text description of substr
Purpose
The "substring" functions return a portion of string, beginning at character position, substring_length characters long. SUBSTR calculates lengths using characters as defined by the input character set. SUBSTRB uses bytes instead of characters. SUBSTRC uses Unicode complete characters. SUBSTR2 uses UCS2 codepoints. SUBSTR4 uses UCS4 codepoints.If position is 0, then it is treated as 1.
If position is positive, then Oracle counts from the beginning of string to find the first character.
If position is negative, then Oracle counts backward from the end of string.
If substring_length is omitted, then Oracle returns all characters to the end of string. If substring_length is less than 1, then a null is returned.
string can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The return value is the same datatype as string. Floating-point numbers passed as arguments to SUBSTR are automatically converted to integers.Examples
The following example returns several specified substrings of "ABCDEFG":SELECT SUBSTR('ABCDEFG',3,4) "Substring"
FROM DUAL;
Substring
---------
CDEFSELECT SUBSTR('ABCDEFG',-5,4) "Substring"
FROM DUAL;Substring
---------
CDEF
A substring of char, starting at character s, length l
The same as SUBSTR, except that 's', 'l' and the return value are expressed in bytes,
use for double-byte char sets
substr 汉字占一位,数字和字母也占一位
substr 按字符来截取substrb按字节来截取
SUBS
----
网易SQL> select substrb('网易',1,2) from dual;SU
--
网
SUBSTR calculates lengths using characters as defined by the input character set.
SUBSTRB calculates lengths using bytes.
就是看数据库字符集,UTF-8的中文就是3个字节