加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 （https://www.lidatong.com.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 百科 > 正文

oralce字符串函数

发布时间：2020-12-12 15:06:56 所属栏目：百科 来源：网络整理
导读：官网链接【由于是国外的网址，网速有点慢，所以这边贴出来了，方便查看】 Oracle/PLSQL String Functions ASCII Get TheASCIIValue Of A Character ASCII(chVARCHAR2CHARACTER SET ANY_CS)RETURNPLS_INTEGER; SELECT ASCII ('A')FROMDUAL; SELECT ASCII ('Z'
Oracle/PLSQL String FunctionsASCIIGet TheASCIIValue Of A CharacterASCII(chVARCHAR2CHARACTER SET ANY_CS)RETURNPLS_INTEGER;SELECTASCII('A')FROMDUAL;
SELECTASCII('Z')FROMDUAL;
SELECTASCII('a')FROMDUAL;
SELECTASCII('z')FROMDUAL;
SELECTASCII(' ')FROMDUAL;CASE Related FunctionsUpper CaseUPPER(chVARCHAR2CHARACTER SET ANY_CS)
RETURNVARCHAR2CHARACTER SET ch%CHARSET;SELECTUPPER('Dan Morgan')FROMDUAL;Lower CaseLOWER(chVARCHAR2CHARACTER SET ANY_CS)
RETURNVARCHAR2CHARACTER SET ch%CHARSET;SELECTLOWER('Dan Morgan')FROMDUAL;Initial Letter Upper CaseINITCAP(chVARCHAR2CHARACTER SET ANY_CS)
RETURNVARCHAR2CHARACTER SET ch%CHARSET;SELECTINITCAP('DAN MORGAN')FROMDUAL;NLS Upper CaseNLS_UPPER(<string_or_column>)SELECTNLS_UPPER('Dan Morgan','NLS_SORT= XDanish')
FROMDUAL;NLS Lower CaseNLS_LOWER(<string_or_column>)SELECTNLS_LOWER('Dan Morgan','NLS_SORT= XFrench')
FROMDUAL;NLS Initial Letter Upper CaseNLS_INITCAP(<string_or_column>)SELECTNLS_INITCAP('DAN MORGAN','NLS_SORT= XGerman')
FROMDUAL;CHRCharacterCHR(nPLS_INTEGER)RETURNVARCHAR2;SELECT(CHR(68) ||CHR(65) ||CHR(78))FROMDUAL;

SELECT(CHR(68) ||CHR(97) ||CHR(110))FROMDUAL;COALESCE
Returns the first non-null occurrenceCOALESCE(<value>,<value>,...)CREATETABLEtest (
col1VARCHAR2(1),
col2VARCHAR2(1),
col3VARCHAR2(1));

INSERTINTOtestVALUES(NULL,'B','C');
INSERTINTOtestVALUES('A',NULL,'C');
INSERTINTOtestVALUES(NULL,'C');

SELECTCOALESCE(col1,col2,col3)FROMtest;CONCATConcatenate

Overload 1

standard.CONCAT(
lefVARCHAR2CHARACTER SET ANY_CS,
rightVARCHAR2CHARACTER SET ANY_CS)
RETURNVARCHAR2CHARACTER SET LEFT%CHARSET;SELECTCONCAT('Dan ','Morgan')FROMDUAL;
Concatenate

Overload 2CONCAT(left INCLOB,right INCLOB)RETURNCLOBset serveroutput on

DECLARE
c1CLOB:=TO_CLOB('Dan ');
c2CLOB:=TO_CLOB('Morgan');
c3CLOB;
BEGIN
SELECTCONCAT(c1,c2)
INTOc3
FROMDUAL;

dbms_output.put_line(c3);
END;
/CONVERTConverts From One Character Set To AnotherCONVERT(<character>,<destination_character_set>,
<source_character_set>)SELECTCONVERT('? ê í ? ? A B C D E','US7ASCII','WE8ISO8859P1')
FROMDUAL;DUMP
Returns aVARCHAR2value containing the datatype code,length in bytes,and internal representation of a valueDUMP(<value> [,<return_format>[,<start_position>[,<length>]]])
8Octal10Decimal16Hexidecimal17Single Characters1008octal notation with the character set name1010decimal notation with the character set name1016hexadecimal notation with the character set name1017single characters with the character set nameset linesize 121
col dmp format a50

SELECTtable_name,DUMP(table_name) DMPFROMuser_tables;

SELECTtable_name,DUMP(table_name,16) DMPFROMuser_tables;

SELECTtable_name,16,7,4) DMPFROMuser_tables;INSTRSee links at page bottomINSTRBLocation of a string,within another string,in bytesINSTRB(
STR1VARCHAR2CHARACTER SET ANY_CS,-- test string
STR2VARCHAR2CHARACTER SET STR1%CHARSET,-- string to locate
POSPLS_INTEGER:= 1,-- position
NTHPOSITIVE:= 1) -- occurrence number
RETURNPLS_INTEGER;SELECTINSTRB('Dan Morgan',' ',1,1)FROMDUAL;INSTRCLocation of a string,in Unicode complete charactersINSTRC(
STR1VARCHAR2CHARACTER SET ANY_CS,-- position
NTHPOSITIVE:= 1) -- occurrence number
RETURNPLS_INTEGER;SELECTINSTRC('Dan Morgan',1)FROMDUAL;INSTR2Location of a string,in UCS2 code pointsINSTR2(
STR1VARCHAR2CHARACTER SET ANY_CS,-- position
NTHPOSITIVE:= 1) -- occurrence number
RETURNPLS_INTEGER;SELECTINSTR2('Dan Morgan',1)FROMDUAL;INSTR4Location of a string,in UCS4 code pointsINSTR4(
STR1VARCHAR2CHARACTER SET ANY_CS,-- position
NTHPOSITIVE:= 1) -- occurrence number
RETURNPLS_INTEGER;SELECTINSTR4('Dan Morgan',1)FROMDUAL;LENGTHString LengthLENGTH(<string_or_column>)SELECTLENGTH('Dan Morgan')FROMDUAL;LENGTHBReturns length in bytesLENGTHB(<char_varchar2_or_clob_value>)SELECTtable_name,LENGTHB(table_name)FROMuser_tables;Note:Additional forms of LENGTH (LENGTHC,LENGTH2,and LENGTH4) are also available.LPADLeft Pad

Overload 1LPAD(
str1VARCHAR2CHARACTER SET ANY_CS,
lenPLS_INTEGER,
PADVARCHAR2CHARACTER SET STR1%CHARSET)
RETURNVARCHAR2CHARACTER SET STR1%CHARSET;SELECTLPAD('Dan Morgan',25,'x')FROMDUAL;Overload 2LPAD(
str1VARCHAR2CHARACTER SET ANY_CS,
lenPLS_INTEGER)
RETURNVARCHAR2CHARACTER SET STR1%CHARSET;SELECTLPAD('Dan Morgan',25)FROMDUAL;Overload 3LPAD(
str1CLOBCHARACTER SET ANY_CS,
lenNUMBER,
PADCLOBCHARACTER SET STR1%CHARSET)
RETURNCLOBCHARACTER SET STR1%CHARSET;TBDOverload 4LPAD(
str1CLOBCHARACTER SET ANY_CS,
len INTEGER)
RETURNCLOBCHARACTER SET STR1%CHARSET;TBDLTRIMLeft Trim

Overload 1LTRIM(
str1VARCHAR2CHARACTER SET ANY_CS := ' ',
tsetVARCHAR2CHARACTER SET STR1%CHARSET)
RETURNVARCHAR2CHARACTER SET STR1%CHARSET;SELECT'->' ||LTRIM(' Dan Morgan ') || '<-'FROMDUAL;Overload 2LTRIM(
STR1VARCHAR2CHARACTER SET ANY_CS := ' ')
RETURNVARCHAR2CHARACTER SET STR1%CHARSET;SELECT'->' ||LTRIM('xxx Dan Morgan ') || '<-'FROMDUAL;

SELECT'->' ||LTRIM('xxxDan Morgan ','x') || '<-'FROMDUAL;MAXThe Maximum String based on the current sort parameterMAX(<character_string>)SELECTMAX(table_name)
FROMuser_tables;MINThe Minimum String based on the current sort parameterMIN(<character_string>)SELECTMIN(table_name)
FROMuser_tables;NLSSORT
Returns the string of bytes used to sort a string.

The string returned is of RAW data typeNLSSORT(<column_name>,'NLS_SORT= <NLS Parameter>);CREATETABLEtest (nameVARCHAR2(15));
INSERTINTOtestVALUES('Gaardiner');
INSERTINTOtestVALUES('Gaberd');
INSERTINTOtestVALUES('G?berd');
COMMIT;

SELECT*FROMtestORDER BYname;

SELECT*FROMtest
ORDER BYNLSSORT(name,'NLS_SORT= XDanish');

SELECT*FROMtest
ORDER BYNLSSORT(name,'NLS_SORT= BINARY_CI');Quote Delimiters
q used to define a quote delimiter for PL/SQLq'<delimiter><string><delimiter>';set serveroutput on

DECLARE
s1VARCHAR2(20);
s2VARCHAR2(20);
s3VARCHAR2(20);
BEGIN
s1 :=q'[Isn't this cool]';
s2 :=q'"Isn't this cool"';
s3 :=q'|Isn't this cool|';

dbms_output.put_line(s1);
dbms_output.put_line(s2);
dbms_output.put_line(s3);
END;
/REPLACESee links at page bottomREVERSEReverseREVERSE(<string_or_column>)SELECTREVERSE('Dan Morgan')FROMDUAL;

SELECTDUMP('Dan Morgan')FROMDUAL;
SELECTDUMP(REVERSE('Dan Morgan'))FROMDUAL;RPADRight Pad

Overload 1RPAD(str1VARCHAR2CHARACTER SET ANY_CS,lenPLS_INTEGER,
padVARCHAR2CHARACTER SET STR1%CHARSET)
RETURNVARCHAR2CHARACTER SET STR1%CHARSET;SELECTRPAD('Dan Morgan','x')FROMDUAL;Overload 2RPAD(str1VARCHAR2CHARACTER SET ANY_CS,lenPLS_INTEGER)
RETURNVARCHAR2CHARACTER SET STR1%CHARSET;SELECTRPAD('Dan Morgan',25) ||'<-'FROMDUAL;RTRIMRight Trim

Overload 1RTRIM(
str1VARCHAR2CHARACTER SET ANY_CS := ' ',
tsetVARCHAR2CHARACTER SET STR1%CHARSET)
RETURNVARCHAR2CHARACTER SET STR1%CHARSET;SELECT'->' ||RTRIM(' Dan Morganxxx') || '<-'FROMDUAL;
SELECT'->' ||RTRIM(' Dan Morganxxx','xxx') || '<-'FROMDUAL;Overload 2RTRIM(
str1VARCHAR2CHARACTER SET ANY_CS := ' ')
RETURNVARCHAR2CHARACTER SET STR1%CHARSET;SELECT'->' ||RTRIM(' Dan Morgan ') || '<-'FROMDUAL;SOUNDEX
Returns Character String Containing The Phonetic Representation Of Another StringRules:
  • Retain the first letter of the string and remove all other occurrences of the following letters: a,e,h,i,o,u,w,y
  • Assign numbers to the remaining letters (after the first) as
    follows:
    b,f,p,v = 1
    c,g,j,k,q,s,x,z = 2
    d,t = 3
    l = 4
    m,n = 5
    r = 6
  • If two or more letters with the same number were adjacent in the original name (before step 1),or adjacent except for any intervening h and w,then omit all but the first.
  • Return the first four bytes padded with 0.

SOUNDEX(chVARCHAR2CHARACTER SET ANY_CS)
RETURNVARCHAR2CHARACTER SET ch%CHARSET;

CREATETABLEtest (
namecolVARCHAR2(15));

INSERTINTOtest (namecol)VALUES('Smith');
INSERTINTOtest (namecol)VALUES('Smyth');
INSERTINTOtest (namecol)VALUES('Smythe');
INSERTINTOtest (namecol)VALUES('Smither');
INSERTINTOtest (namecol)VALUES('Smidt');
INSERTINTOtest (namecol)VALUES('Smick');
INSERTINTOtest (namecol)VALUES('Smiff');
COMMIT;

SELECTnamecol,SOUNDEX(namecol)FROMtest;
-- Thanks Frank van Bortel for the idea for the above

SELECT*
FROMtest
WHERESOUNDEX(namecol) =SOUNDEX('SMITH');SUBSTRSee links at page bottomSUBSTRBReturns a substring countingbytesrather thancharactersSUBSTRB(
STR1VARCHAR2CHARACTER SET ANY_CS,
POSPLS_INTEGER,-- starting position
LENPLS_INTEGER:= 2147483647) -- number of characters
RETURNVARCHAR2CHARACTER SET STR1%CHARSET;See Demos on theSubstringPageSUBSTRCReturns a substring within another string,using Unicode code pointsSUBSTRC(
STR1VARCHAR2CHARACTER SET ANY_CS,-- starting position
LENPLS_INTEGER:= 2147483647) -- number of characters
RETURNVARCHAR2CHARACTER SET STR1%CHARSET;See Demos on theSubstringPageSUBSTR2Returns a substring within another string,using UCS2 code pointsSUBSTR2(
STR1VARCHAR2CHARACTER SET ANY_CS,-- starting position
LENPLS_INTEGER:= 2147483647) -- number of characters
RETURNVARCHAR2CHARACTER SET STR1%CHARSET;See Demos on theSubstringPageSUBSTR4Returns a substring within another string,using UCS4 code pointsSUBSTR4(
STR1VARCHAR2CHARACTER SET ANY_CS,-- starting position
LENPLS_INTEGER:= 2147483647) -- number of characters
RETURNVARCHAR2CHARACTER SET STR1%CHARSET;See Demos on theSubstringPageTRANSLATESee links at page bottomTREATChanges The Declared Type Of An ExpressionTREAT(<expression> AS REF schema.type))SELECTname,TREAT(VALUE(p)ASemployee_t).salary SALARY
FROMpersons p;TRIM (variations are LTRIM and RTRIM)Trim SpacesTRIM(<string_or_column>)SELECT' Dan Morgan 'FROMDUAL;

SELECTTRIM(' Dan Morgan ')FROMDUAL;Trim Other CharactersTRIM(<character_to_trim>FROM<string_or_column>)SELECTTRIM('D'FROM'Dan Morgan')FROMDUAL;Trim By CHR valueTRIM(<string_or_column>)SELECTASCII(SUBSTR('Dan Morgan',1))FROMDUAL;

SELECTTRIM(CHR(68)FROM'Dan Morgan')FROMDUAL;Vertical BarsAlso known as Pipes<first_string>||<second_string>SELECT'Dan'||' '||'Morgan'FROMDUAL;

WITHALIAS

SELECT'Dan'||' '||'Morgan' NAMEFROMDUAL;
or
SELECT'Dan'||' '||'Morgan'ASNAMEFROMDUAL;VSIZEByte SizeVSIZE(e INVARCHAR2)RETURNNUMBERSELECTVSIZE('Dan Morgan')FROMDUAL;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

官网链接【由于是国外的网址，网速有点慢，所以这边贴出来了，方便查看】

    相关内容
      推荐文章
        站长推荐
        热点阅读

          【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

          建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

          Copygight © 2008-2022 https://www.lidatong.com.cn/ All Rights Reserved. 李大同