Volumen de inyección

String-manipulationfunctionsacceptargumentsoftypeCHAR, NCHAR, VARCHAR,NVARCHAR,orLVARCHAR.Youcanusea string-manipulation functionanywhereyouuseanexpression.

Thefollowingfunctionsconvertbetweenupperand lowercaselettersina characterstring:

v LOWER

v UPPER

v INITCAP

Thefollowingfunctionsmanipulatecharacterstringsinvariousways:

v REPLACE v SUBSTR v SUBSTRING v LPAD v RPAD

Youcannotoverloadanyofthestring-manipulationfunctionstohandle extendeddatatypes.

Usingthe LOWERFunction

UsetheLOWERfunctionto replaceeveryuppercaseletter inacharacter stringwith alowercaseletter.TheLOWERfunctionaccepts anargumentofa characterdatatype andreturnsavalue ofthesamedatatypeastheargument youspecify.

Figure4-56 usestheLOWERfunctiontoconvertanyuppercaselettersina characterstringtolowercaseletters.

Usingthe UPPERFunction

UsetheUPPERfunction toreplaceeverylowercase letterina characterstring withanuppercase letter.TheUPPERfunctionacceptsanargument ofa characterdatatype andreturnsavalue ofthesamedatatypeastheargument youspecify.

Figure4-58 usestheUPPERfunction toconvertanylowercase lettersina characterstringtouppercaseletters.

Usingthe INITCAPFunction

UsetheINITCAPfunction toreplacethefirstletter ofeverywordina characterstringwithan uppercaseletter.TheINITCAPfunctionassumesa newwordwheneverthefunctionencountersa letterthatisprecededbyany SELECT manu_code, LOWER(manu_code)

FROM items WHERE order_num = 1018 Figure4-56.Query manu_code (expression) PRC prc KAR kar PRC prc SMT smt HRO hro

Figure4-57.QueryResult

SELECT call_code, UPPER(code_descr) FROM call_type

Figure4-58.Query

call_code (expression)

B BILLING ERROR D DAMAGED GOODS

I INCORRECT MERCHANDISE SENT L LATE SHIPMENT

O OTHER

characterotherthana letter.The INITCAPfunction acceptsanargumentof a characterdatatype andreturnsavalue ofthesamedatatypeastheargument youspecify.

Figure4-60 usestheINITCAPfunctiontoconvertthefirst letterofevery wordina characterstringtoanuppercase letter.

Usingthe REPLACEFunction

UsetheREPLACEfunction toreplacea certainsetofcharacters inacharacter stringwith othercharacters.

InFigure4-62,theREPLACEfunctionreplacestheunitcolumnvalueeach withitemforeveryrowthatthequeryreturns.Thefirst argumentofthe REPLACEfunction istheexpressiontobeevaluated.Thesecond argument specifiesthecharactersthatyouwanttoreplace.Thethirdargumentspecifies anewcharacterstringtoreplacethecharactersremoved.

SELECT INITCAP(description) FROM stock WHERE manu_code = "ANZ"

Figure4-60.Query (expression) Tennis Racquet Tennis Ball Volleyball Volleyball Net Helmet Golf Shoes 3 Golf Balls Running Shoes Watch Kick Board Swim Cap

Figure4-61.QueryResult

SELECT stock_num, REPLACE(unit,"each", "item") cost_per, unit_price

FROM stock

WHERE manu_code = "HRO"

Usingthe SUBSTRINGandSUBSTRFunctions

YoucanusetheSUBSTRINGandSUBSTRfunctionstoreturna portionofa characterstring.Youspecifythestartpositionand length(optional)to

determinewhichportionof thecharacterstringthefunction returns.

Usingthe SUBSTRINGFunction

YoucanusetheSUBSTRINGfunctiontoreturn someportionofacharacter string.Youspecifythestartpositionand length(optional)todeterminewhich portionofthecharacterstringthefunctionreturns.Youcanspecifyapositive ornegativenumber forthestart position.Astartpositionof1 specifiesthat theSUBSTRING functionbeginsfromthefirst positioninthestring. When thestart positioniszero(0)ora negativenumber,theSUBSTRING function countsbackward fromthebeginningofthestring.

Figure4-64 showsanexampleoftheSUBSTRINGfunction, whichreturnsthe firstfourcharactersforanysnamecolumnvaluesthatthequeryreturns. In thisexample,theSUBSTRINGfunctionstartsat thebeginningofthestring andreturnsfourcharacterscountingforwardfromthestart position.

stock_num cost_per unit_price

1 case $250.00 2 case $126.00 4 case $480.00 7 case $600.00 110 case $260.00 205 case $312.00 301 item $42.50 302 item $4.50 304 box $280.00 305 case $48.00 309 case $40.00 312 box $72.00

Figure4-63.QueryResult

SELECT sname, SUBSTRING(sname FROM 1 FOR 4) FROM state WHERE code = "AZ"

InFigure4-66, theSUBSTRINGfunction specifiesa start positionof6 but doesnotspecifythelength.Thefunctionreturnsacharacterstringthat extendsfromthesixthpositiontotheendofthestring.

InFigure4-68,theSUBSTRINGfunction returnsonlythefirstcharacterfor anysnamecolumnvalue thatthequeryreturns. FortheSUBSTRING function,a start positionof -2counts backwardthreepositions (0,-1, -2) fromthestart positionofthestring(fora startpositionof0,thefunction countsbackward onepositionfromthebeginningofthestring).

Usingthe SUBSTRFunction

TheSUBSTRfunction servesthesamepurposeastheSUBSTRING function, butthesyntaxofthetwofunctionsdiffers.

sname (expression)

Arizona Ariz

Figure4-65.QueryResult

SELECT sname, SUBSTRING(sname FROM 6) FROM state WHERE code = "WV"

Figure4-66.Query

sname (expression)

West Virginia Virginia

Figure4-67.QueryResult

SELECT sname, SUBSTRING(sname FROM -2 FOR 4) FROM state WHERE code = "AZ"

Figure4-68.Query

sname (expression)

Arizona A

Toreturnaportion ofacharacterstring,specifythestartpositionand length (optional)todeterminewhichportion ofthecharacterstringtheSUBSTR functionreturns. ThestartpositionthatyouspecifyfortheSUBSTRfunction canbe apositiveora negativenumber.However, theSUBSTRfunctiontreats anegativenumberinthestartpositiondifferently thandoestheSUBSTRING function.Whenthestart positionisa negativenumber,theSUBSTRfunction countsbackward fromtheendofthecharacterstring, whichdependsonthe lengthofthestring,notthecharacterlengthof awordorvisible characters thatthestringcontains.TheSUBSTRfunctionrecognizeszero (0)or1in the startpositionasthefirstpositioninthestring.

Figure4-70 showsanexampleoftheSUBSTRfunctionthatincludesa negativenumberfor thestart position.Given astart positionof -15,the SUBSTRfunction countsbackward15positionsfromtheendofthestringto findthestartpositionandthen returnsthenext fivecharacters.

Tousea negativenumberforthestart position,youneedtoknowthelength ofthevaluethatisevaluated.ThesnamecolumnisdefinedasCHAR(15),so aSUBSTRfunctionthatacceptsan argumentoftypesname canuseastart positionof0,1,or-15 forthefunctionto returna characterstringthat begins fromthefirst positioninthestring.

Figure4-72 returnsthesameresult asFigure4-70.

Usingthe LPADFunction

UsetheLPADfunctiontoreturn acopyofa stringthathasbeenleftpadded witha sequenceofcharactersthatare repeatedasmanytimesasnecessaryor truncated,dependingonthespecified lengthofthepaddedportionofthe SELECT sname, SUBSTR(sname, -15, 5) FROM state

WHERE code = "CA"

Figure4-70.Query

sname (expression)

California Calif

Figure4-71.QueryResult

SELECT sname, SUBSTR(sname, 1, 5) FROM state WHERE code = "CA

string.Specifythesourcestring,thelengthofthestringtobe returned,and thecharacterstringtoserveaspadding.

Thedatatypeofthesourcestringand thecharacterstringthatservesas paddingcanbeanydatatype thatconvertstoVARCHAR orNVARCHAR.

Figure4-73 showsanexampleoftheLPADfunction withaspecified lengthof 21bytes.Becausethesourcestringhasalength of15bytes(snameisdefined asCHAR(15)), theLPADfunctionpadsthefirstsix positionstotheleftofthe sourcestring.

Usingthe RPADFunction

UsetheRPADfunction toreturna copyofastringthathasbeen right paddedwitha sequenceofcharactersthatare repeatedasmanytimesas necessaryortruncated,dependingonthespecifiedlengthofthepadded portionofthestring.Specifythesourcestring,thelengthofthestringtobe returned,andthecharacterstringtoserveaspadding.

Thedatatypeofthesourcestringand thecharacterstringthatservesas paddingcanbeanydatatype thatconvertstoVARCHAR orNVARCHAR.

Figure4-75 showsanexampleoftheRPADfunctionwith aspecifiedlengthof 21bytes.Becausethesourcestringhasalength of15bytes(snameisdefined asCHAR(15)), theRPADfunction padsthefirstsix positionstotherightof thesourcestring.

SELECT sname, LPAD(sname, 21, "-") FROM state

WHERE code = "CA" OR code = "AZ"

Figure4-73.Query

sname (expression)

California ---California Arizona ---Arizona

Figure4-74.QueryResult

SELECT sname, RPAD(sname, 21, "-") FROM state

WHERE code = "WV" OR code = "AZ"