STBSTR(,<i>[,]) 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
一个汉字占2个字节 好不??樓上的 SQL> select substrb('你真的好吗',-2) from dual; 吗
to 4楼的 你和toad仔细看一下,其实返回了三个字节" 吗" 只不过"吗"前面的那个字节显示不出来,为空字符
to 4楼的 你和toad仔细看一下,其实返回了三个字节" 吗" 只不过"吗"前面的那个字节显示不出来,所以显示为空格字符
see it!Syntax 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
SUBSTRB(char, s[,l]) 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
例如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个字节