INSTH Function

InThesesyntaxes:

char_value

representsihesinglecharacter

whoso

case haslobe

changed string value

^represents(hecompletesiringpassedtochangethecase.

Thefollowinglistingdemonstrate

how

the

LOWER

and

UPPER

functionswork.

Listing 3.5:Using

LOWER

and

UPPER

(unctions

InListing3.5,firs!

we

havedeclaredtwovariablesnaro.and namlof Ihe

VAPCHAR2

datatype.

After declarihg all variables,

we

have used ihe

SELECT

slalemenT to fetch the

DNAME

(departmentname)into

nam

from IhelabfeDEPT,where

DEPTNO

(departmenlnumber)isequal 1020.Then,

we

haveapplied Ihe

LOWER

funcliononIhesiringstoredinthe naipvariable,and usedThe

SELECT

statementlofetchIhe

DNAME

intovarrablenaml where

DEPTNO

isequalto 50. ATlasi,

we

haveappliedihe

upper

functiononihestring slorcdinIhenaml ^variable.

Theoutput,

when we

execuie theprogram

shown

inLining3.5,appears asfollows;

Output:

In Ihisoutout, Ihefirstoulpul(research) isinlowercaselettersbecauseof the

LOWER

^function, whereasthesecondoulpuli

human RESOURCE)

isinuppercaseletters,which^isiheresultofihe

UPPER

funclion.

INSTH Function

This functionisusedId findIhe position ofasubstringinthemainsiring.This function returns zeroincase ihesubstringisnot presentinthemainstring

SyntaxforIheTtJSTR functionis:

INSTR Cstringl_VB7ue, stn"ngZ_value, itarring_posit!on, nth_pre«nce)

;

53

In(hisiynla\:

u

.-r.ri!-..;1 vi_i:chthemainstringinwhichthe.¹position of[he^substringhastohi.'lound.

.u- "

.- i-,ihh' Mil>sinr)j>vUi^seposiliunhast<>be t,u(hedin(hemain^trui^

;.--]>-r:]q c:^--.-irr.representsthepositioninmainstringfromwhichtheseardi hasto bestarled. It an optionalHr^urrient,liyou don't¹specit\ the ..rt r.g po.;:ri tile 'e,ir<!>wi|!-btiirifrom[heI jr^.t(h,ira<lt^jrnllht^jiii.iinsiting.

:,-.n_fj:c

-

r.-'-representsthenthposition ot thesubstring

m

the m.iin string

Ii-s [ i r i

j.* i.ltsho^s hm%Ihe I!'SI-hinttioiiworks.

ListingJ,f>:l

f

LSQL

propram(ouse

INJ7R kmc

(ion

Infisiinj; !h.lustivehave dellared,1variable

uli:

ofthe'.'A!<l\ data^pe,andthen

we haw

letdied ihe L " :(topart

men

^r location^ into lhat variable irom Ihe (able where

.I- ikOfjki.ilii>r'O

\nw.T:i."T"' iiinclinnhasbeenappliedonIhe ]i^-variablethreetimesknthreerc-suils^

li.tin^-tirsiins(an<r.tm\\ !^<>parameters

—

:hemainstringond^jhstnn^--have-beenpasser!

ItvvjIIshow1hefirsl

ok

urreru.e uf suhslrrnjJ

m

She

mam

string.

J

Setondlv.

we haw

parsed 4p.iramelerv.Thislime

uo

hateaddedHie positionIroniwhere.1

sedirhim ihe KubstrirtjJ haslahe started in ihe main string. Ik-re die ihirdand laurth parametersan^{1 i},\.whichmetingthattheseardihast(jbeslarledTrom ihefirstletteroiIhe slnne.and uptothelirs;occurrenceotthe substringinthemainstring.

_J AlllnriSin-t.ir'ci Iliep.nariieler pass.nl,iresameasthein set"oridoneelu eptth.ithere

we

are searthiim The^ubMNrYu;dpJoilsi'"'occurrenceinthemainstring.

TlieriLdfuiiniIMuni i'ii'.Hisictllnivs:

Output:

DALLAS

£

PL/SQL

In lhi^L ouifiLti, \<ia<hiu -ee that (here *irt^x four results. The firsl result, .ai

'^

showsihe department Irn^llriri. whir h

ue

usehlsllif in.nii slnnj;

m

this Iehktion. Tfie sero^nrlresuN ^.' inrlil.iles(he_{I i} L<H<inrenreotihe 'so^lj^triri^iirl(henlaiiri^1111114. 1heihirdresultiss.htk¹,ls

54

3: Butlt-in Fnttctiotw

thesecondone becauseof thefourthparameterduoiowhichthesubstringhasbeensearched uptoiBfirstoccurrence.Inthefourthresult,thepositionreturned by^thisfunctionis!j, which represents(hesecond occurrenceof'A'inthe

mam

string.

functions

arcusedtotrimthestringentered as aparameter.The

LTRIM

functiontrimsthe stringfromthe^leftsideby removingthecharacterspecifiedas parameterⁱⁿ thisfunction.The

RTRIM

function,onthe otherhand,trimsthestringfrom therightside.

Syntaxforthe

LTRIM

functionis:

. . LTRltf(str1ng_valtie, str1ng_tr1m)

;

Syntaxforthe

RTRIM

functionis:

, KTR1H (3triflQ_valtie» stMng^trlm]

;

Inboth of thepreceding syntaxes

stcingvslue

represents Themainstring,whichhaslobetrimmed.

scring_trim

specifiesthestringthat willberemovedfromtheleftside or right side ofthe mainstringdependingonthe functionyouuse.Inboth[hefunctions thesecondargumentis

optionalandifyoudon'tspecifythesecondparameter thenall leading(LTRIMfunction)or trailing(RTRtMfunction)spaceswillberemovedfrom thesiring.

The

followinglistingshows

how

thisfundtonworks.

Listing 3,7:Using

LTRIM

and

RTRIM

functions

In Listing 3.7, first,

we

havedeclared fourvariables,nam,d_nam, namZ, and d naml. The variable

nam

hasbeen initializedwith a stringhaving some[railingblankspaces while the variable

nam2

hasbeeninitializedwith astring,whichhas leadingblank spaces.^Afterthis,the

DNAME

hasbeenfetched intothe

d_nam

and

d_naml

variables fromthe table

DEFT

where

DEPTNO

isequalto50. Atlast,

we

haveapplied the

LTRIM

and

RTRIM

functionsonthestrings

indifferentvariables.

55

Theoutput ofLisling3.7isasfollows:

The

outpulofLining 3.7showsfourresults. Inthefirstresull,youcan seelhatall thetrailing spaceshavebeenremoved fromthestringbecauseofthe

LTRIM

^function,

tntheihird resultyou canseelhatallleadingspaceshavebeenremoved bythe

RTRIM

^function,

tn(hesecondresull,thedepartment

name human resource

hasbeenIrimmedfrom theleft sidebecauseof theuse of the

LTRIM

function;andthefourth resultshowsthatihestring

human

resource

hasbeen trimmedfrom therightside.

In document Oracle PL/SQL Programming (Page 60-63)