Oracle中的varchar2類型及其字符串截取

Oracle中的varchar2類型及其字符串截取1、varchar2類型介紹1.1Oracle數(shù)據(jù)庫常?的數(shù)據(jù)類型介紹varchar2類型的同時，我們順便了解下Oracle數(shù)據(jù)庫?持的其他數(shù)據(jù)類型。如下摘?Oracle官??檔Built-inDataTypeSummary，簡單介紹了Oracle中常?的數(shù)據(jù)類型。CDatoaDescriptionMemodTypeevarchar2中指定的長度參數(shù)，指的是最多可以存儲的長度，實際存儲超過這個長度的內(nèi)容會報VARCHAR2(size[BYTE|Variable-lengthcharacterstringhavingmaximumlengthsizebytesorcharacters.Maximumsizeis4000bytesorcharacters,andminimumis1byteor1character.YoumustspecifysizeforVARCHAR2.BYTEindicatesthatthecolumnwillhavebytelengthsemantics.CHARindicatesthatthecolumnwillhavecharactersemantics.錯。類型中var的意思是說，存儲?于該長度的內(nèi)容時，實際占?的存儲根據(jù)具體存儲內(nèi)容長度?定。?如定義varchar2(10)實際存儲內(nèi)容為3就占?長度為3的存儲1CHAR])空間。相?于定長的char，可以節(jié)省存儲空間。Variable-lengthUnicodecharacterstringhavingmaximumlengthsizecharacters.ThenumberofbytescanbeuptotwotimessizeforNVARCHAAL16UTF16encodingandthreetimessizeforUTF8encoding.R2(sizMaximumsizeisdeterminedbythenationalcharactersetdefinition,1主要是?來存儲Unicode字符的e)withanupperlimitof4000bytes.YoumustspecifysizeforNVARCHAR2.NUMNumberhavingprecisionpandscales.TheprecisionpcanrangeBER[from1to38.Thescalescanrangefrom-84to127.Bothprecision2(p[,s])]andscaleareindecimaldigits.ANUMBERvaluerequiresfrom1to22bytes.AsubtypeoftheNUMBERdatatypehavingprecisionp.AFLOATFLOAvalueisrepresentedinternallyasNUMBER.Theprecisionpcan28T[(p)]rangefrom1to126binarydigits.AFLOATvaluerequiresfrom1to22bytes.LONGCharacterdataofvariablelengthupto2gigabytes,or231-1bytes.Providedforbackwardcompatibility.表?的?期范圍可以是公元前4712年1?1??公元9999年12?31?。YoucanaddandsubtractconstantstoandfromaDATEvalue,andthesenumberswillbeinterpretedasnumbersofdays.Forexample,SYSDATE+1willbetomorrow.YoucannotValiddaterangefromJanuary1,4712BC,toDecember31,9999AD.Thedefaultformatisdeterminedexplicitlybythe12DATNLS_DATE_FORMATparameterorimplicitlybytheNLS_TERRITORYEparameter.Thesizeisfixedat7bytes.ThisdatatypecontainsthedatetimefieldsYEAR,MONTH,DAY,HOUR,MINUTE,andSECOND.Itdoesnothavefractionalsecondsoratimezone.multiplyordivideDATEvalues.100BINARY_FLOAT32-bitfloatingpointnumber.Thisdatatyperequires4bytes.BINA1RY_DCDat64-bitfloatingpointnumber.Thisdatatyperequires8bytes.Description0oOUBLa1EdTypMemoeeYear,month,anddayvaluesofdate,aswellashour,minute,andsecondvaluesoftime,wherefractional_seconds_precisionisthenumberofdigitsinthefractionalpartoftheSECONDdatetimefield.Acceptedvaluesoffractional_seconds_precisionare0to9.Thedefaultis6.ThedefaultformatisdeterminedexplicitlybytheNLS_TIMESTAMP_FORMATparameterorimplicitlybytheNLS_TERRITORYparameter.Thesizeis7or11bytes,dependingontheprecision.ThisdatatypecontainsthedatetimefieldsYEAR,MONTH,DAY,HOUR,MINUTE,andSECOND.Itcontainsfractionalsecondsbutdoesnothaveatimezone.TIMESTAMP180[(fractional_seconds_precision)]TIMESTAMPAllvaluesofTIMESTAMPaswellastimezonedisplacementvalue,wherefractional_seconds_precisionisthenumberofdigitsinthefractionalpartoftheSECONDdatetimefield.Acceptedvaluesare0to9.Thedefaultis6.Thedefaultformatisdeterminedexplicitlyby[(fractional_secon181ds_prtheNLS_TIMESTAMP_FORMATparameterorimplicitlybytheecision)]NLS_TERRITORYparameter.Thesizeisfixedat13bytes.ThisdatatypecontainsthedatetimefieldsYEAR,MONTH,DAY,HOUR,WITHMINUTE,SECOND,TIMEZONE_HOUR,andTIMEZONE_MINUTE.IthasTIMEZONEfractionalsecondsandanexplicittimezone.TIMESTAMP[(fractional_AllvaluesofTIMESTAMPWITHTIMEZONE,withthefollowingseconds_prexceptions:Dataisnormalizedtothedatabasetimezonewhenitis231storedinthedatabase.Whenthedataisretrieved,usersseetheecisiodatainthesessiontimezone.Thedefaultformatisdeterminedn)]explicitlybytheNLS_TIMESTAMP_FORMATparameterorimplicitlyWITHbytheNLS_TERRITORYparameter.Thesizeis7or11bytes,LOCAdependingontheprecision.LTIMEZONEINTERVALYEAR182[(yearStoresaperiodoftimeinyearsandmonths,whereyear_precisionis_precithenumberofdigitsintheYEARdatetimefield.Acceptedvaluesaresion)]0to9.Thedefaultis2.Thesizeisfixedat5bytes.TOMONTHINTERVALDAY[(day_precisStoresaperiodoftimeindays,hours,minutes,andseconds,whereion)]day_precisionisthemaximumnumberofdigitsintheDAYdatetimeTO183field.Acceptedvaluesare0to9.Thedefaultis2.SECOfractional_seconds_precisionisthenumberofdigitsinthefractionalNDpartoftheSECONDfield.Acceptedvaluesare0to9.Thedefaultis[(fracti6.Thesizeisfixedat11bytes.onal_seconCDatds_praoecisiodTypn)]DescriptionMemoee23RAW(size)Rawbinarydataoflengthsizebytes.Maximumsizeis2000bytes.YoumustspecifysizeforaRAWvalue.LONG24Rawbinarydataofvariablelengthupto2gigabytes.RAWBase64stringrepresentingtheuniqueaddressofarowinitstable.ThisdatatypeisprimarilyforvaluesreturnedbytheROWIDpseudocolumn.69ROWIDUROWID[(size)]208Base64stringrepresentingthelogicaladdressofarowofanindex-organizedtable.TheoptionalsizeisthesizeofacolumnoftypeUROWID.Themaximumsizeanddefaultis4000bytes.CHARFixed-lengthcharacterdataoflengthsizebytesorcharacters.Maximumsizeis2000bytesorcharacters.Defaultandminimumsizeis1byte.BYTEandCHARhavethesamesemanticsasforVARCHAR2.[(size[BYTE|96CHAR])]Fixed-lengthcharacterdataoflengthsizecharacters.ThenumberofNCHbytescanbeuptotwotimessizeforAL16UTF16encodingandAR[(sithreetimessizeforUTF8encoding.Maximumsizeisdeterminedby96ze)]thenationalcharactersetdefinition,withanupperlimitof2000bytes.Defaultandminimumsizeis1character.Acharacterlargeobjectcontainingsingle-byteormultibyte112CLOcharacters.Bothfixed-widthandvariable-widthcharactersetsareBsupported,bothusingthedatabasecharacterset.Maximumsizeis(4gigabytes-1)*(databaseblocksize).AcharacterlargeobjectcontainingUnicodecharacters.Bothfixed-widthandvariable-widthcharactersetsaresupported,bothusingthedatabasenationalcharacterset.Maximumsizeis(4gigabytes-1)*(databaseblocksize).Storesnationalcharactersetdata.112NCLOB113BLOBAbinarylargeobject.Maximumsizeis(4gigabytes-1)*(databaseblocksize).114Containsalocatortoalargebinaryfilestoredoutsidethedatabase.EnablesbytestreamI/OaccesstoexternalLOBsresidingonthedatabaseserver.Maximumsizeis4gigabytes.BFILE需要注意的是，如果在聲明varchar2類型長度的時候，沒有顯式指定是BYTE還是CHAR，缺省的情況取決于數(shù)據(jù)庫參數(shù)NLS_LENGTH_SEMANTICS。具體可以通過如下sql查詢，如下是?個例?：selectname,valuefromv$parameterwherename='nls_length_semantics';結(jié)果：NAMEVALUEnls_length_semanticsBYTENAMEVALUE在這個例?中，缺省的情況，就是按照字節(jié)長度來計算。1.2Oracle中varchar2和varchar的區(qū)別截?現(xiàn)在，varchar和varchar2是沒有區(qū)別的。按照ANSI的標(biāo)準(zhǔn)，varchar在Oracle中是被保留的，?來后續(xù)?持區(qū)分NULL和空字符串。VARCHAR2現(xiàn)在不區(qū)分NULL和空字符串，以后也不會區(qū)分。如果程序?qū)τ贜ULL和空字符串等同有依賴，就應(yīng)該使?varchar2類型。2、Oracle中截取字符串2.1substr函數(shù)官?說明如下：{SUBSTR|SUBSTRB|SUBSTRC|SUBSTR2|SUBSTR4}(char,position[,substring_length])TheSUBSTRfunctionsreturnaportionofchar,beginningatcharacterposition,substring_lengthcharacterslong.SUBSTRcalculateslengthsusingcharactersasdefinedbytheinputcharacterset.SUBSTRBusesbytesinsteadofcharacters.SUBSTRCusesUnicodecompletecharacters.SUBSTR2usesUCS2codepoints.SUBSTR4usesUCS4codepoints.Ifpositionis0,thenitistreatedas1.Ifpositionispositive,thenOracleDatabasecountsfromthebeginningofchartofindthefirstcharacter.Ifpositionisnegative,thenOraclecountsbackwardfromtheendofchar.Ifsubstring_lengthisomitted,thenOraclereturnsallcharacterstotheendofchar.Ifsubstring_lengthislessthan1,thenOraclereturnsnull.charcanbeanyofthedatatypesCHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,orNCLOB.Bothpositionandsubstring_lengthmustbeofdatatypeNUMBER,oranydatatypethatcanbeimplicitlyconvertedtoNUMBER,andmustresolvetoaninteger.Thereturnvalueisthesamedatatypeaschar.Floating-pointnumberspassedasargumentstoSUBSTRareautomaticallyconvertedtointegers.?法介紹：substr函數(shù)：截取字符串語法：SUBSTR(string，start，[length])string：表?源字符串，即要截取的字符串。start：開始位置，從1開始查找。如果start是負數(shù)，則從string字符串末尾開始算起。length：可選項，表?截取字符串長度。下?是?個例?：SELECT'截取所有字符串'func,'SUBSTR(''HelloSQL!'',1)'expr,SUBSTR('HelloSQL!',1)expr_valFROMdualunionallSELECT'從第2個字符開始，截取到末尾'func,'SUBSTR(''HelloSQL!'',2)'expr,SUBSTR('HelloSQL!',2)expr_valFROMdualunionallSELECT'從倒數(shù)第4個字符開始，截取到末尾'func,'SUBSTR(''HelloSQL!'',-4)'expr,SUBSTR('HelloSQL!',-4)expr_valFROMdualunionallSELECT'從第3個字符開始，截取6個字符'func,'SUBSTR(''HelloSQL!'',3,6)'expr,SUBSTR('HelloSQL!',3,6)expr_valFROMdualunionallSELECT'從倒數(shù)第4個字符開始，截取3個字符'func,'SUBSTR(''HelloSQL!'',-4,3)'expr,SUBSTR('HelloSQL!',-4,3)expr_valFROMdualunionallSELECT'從截取開始位置超過字符串長度'func,'SUBSTR(''HelloSQL!'',20,3)'expr,SUBSTR('HelloSQL!',20,3)expr_valFROMdual結(jié)果如下：substr例?注：上圖中的-表?空。2.2根據(jù)指定字符出現(xiàn)的位置截取字符串這?需要?到oracle中的instr函數(shù)，?法如下。instr函數(shù)：返回?字符串在源字符串中的位置語法：INSTR(string，child_string，[start]，[show_time])string：表?源字符串。child_string：?字符串，即要查找的字符串。start：可選項，開始位置，默認從1開始。如果為負數(shù)，則從右向左檢索。show_time：可選項，表??字符串第?次出現(xiàn)在源字符串當(dāng)中，默認第1次，負數(shù)則報錯。返回指定字符出現(xiàn)的位置從1開始，如果找不到返回0下?是?個例?：SELECT'查找字符串中l(wèi)出現(xiàn)的位置'func,'INSTR(''HelloSQL!'',''l'')'expr,INSTR('HelloSQL!','l')expr_valFROMdualunionallSELECT'查找字符串中H出現(xiàn)的位置'func,'INSTR(''HelloSQL!'',''H'')'expr,INSTR('HelloSQL!','H')expr_valFROMdualunionallSELECT'查找字符串中l(wèi)出現(xiàn)的位置'func,'INSTR(''HelloSQL!'',''l'',1)'expr,INSTR('HelloSQL!','l',1)expr_valFROMdualunionallSELECT'查找字符串中l(wèi)l出現(xiàn)的位置'func,'INSTR(''HelloSQL!'',''ll'')'expr,INSTR('HelloSQL!','ll')expr_valFROMdualunionallSELECT'查找字符串中aa出現(xiàn)的位置'func,'INSTR(''HelloSQL!'',''aa'')'expr,INSTR('HelloSQL!','aa')expr_valFROMdualunionallSELECT'查找字符串中l(wèi)第2次出現(xiàn)的位置'func,'INSTR(''HelloSQL!'',''l'',1,2)'expr,INSTR('HelloSQL!','l',1,2)expr_valFROMdual結(jié)果如下：instr例?結(jié)合instr?如我們想截取某個字符出現(xiàn)位置之前的字符，下?是?個例?。--截取空格前的內(nèi)容SELECTSUBSTR('HelloSQL!',1,INSTR('HelloSQL!','')-1)FROMdual--Hello--截取空格后的內(nèi)容SELECTSUBSTR('HelloSQL!',INSTR('HelloSQL!','')+1)FROMdual--SQL!2.3substrb按字節(jié)截取字符串2.3.1Oracle數(shù)據(jù)中?占?個字節(jié)可以通過查看如下參數(shù)(查詢當(dāng)前數(shù)據(jù)庫的字符集)來了解當(dāng)前的oracle環(huán)境中?個漢字占?個字節(jié)：SELECT*FROMv$nls_parametersWHEREPARAMETER='NLS_CHARACTERSET';如果value=ZHS16GBK，那么?個漢字占?2個字節(jié)；如果value=AL32UTF8，那么?個漢字占?3個字節(jié)。另外?個參數(shù)也可以：selectuserenv('language')fromdual;結(jié)果如下：列USERENV('LANGUAGE')值A(chǔ)MERICAN_AMERICA.AL32UTF8這?AL32UTF8說明當(dāng)前環(huán)境下，?個漢字占3個字節(jié)。實際上，也可以通過語句進?測試，如下：select'length(''美喵泡泡'')'expr,length('美喵泡泡')expr_valfromdualunionallselect'lengthb(''美喵泡泡'')'expr,lengthb('美喵泡泡')expr_valfromdual結(jié)果如下：exprexpr_vallength('美喵泡泡')4lengthb('美喵泡泡')12exprexpr_val2.3.2Oracle多字節(jié)數(shù)據(jù)截取如下是?個跨字節(jié)截取的例?：select'dump(''美喵泡泡'')'expr,dump('美喵泡泡')expr_valfromdualunionallselect'substrb(''美喵泡泡'',1,3)'expr,substr