Oracle | String (Computer Science) | Oracle Database

Please download to get full document.

View again

of 22
All materials on our website are shared by users. If you have any questions about copyright issues, please report us to resolve them. We are always happy to assist you.
Information Report
Category:

Documents

Published:

Views: 3 | Pages: 22

Extension: PDF | Download: 0

Share
Related documents
Description
Oracle For Beginners Page : 1 Chapter 7 STRING, CONVERSION, AND FUNCTIONS MISCELLANEOUS FUNCTIONS String functions Conversion functions Miscellaneous functions In the last chapter we have seen how to use arithmetic and date functions. In this chapter let us see how to use string, conversion and miscellaneous functions. String Functions String functions are functions that manipulate a set of characters. A set of characters is a string. For example, the name of the company, the address of a pe
Transcript
  Oracle For Beginners Page : 1  srikanthtechnologies.com Chapter 7 STRING, CONVERSION,STRING, CONVERSION,STRING, CONVERSION,STRING, CONVERSION, ANDANDANDANDMISCELLANEOUS FUNCTIMISCELLANEOUS FUNCTIMISCELLANEOUS FUNCTIMISCELLANEOUS FUNCTIONSONSONSONS    String functions    Conversion functions    Miscellaneous functionsIn the last chapter we have seen how to use arithmetic and date functions. In this chapter letus see how to use string, conversion and miscellaneous functions. String Functions String functions are functions that manipulate a set of characters. A set of characters is astring. For example, the name of the company, the address of a person all these are examplesof a string. CHAR and VARCHAR data types contain strings. Let us first see how toconcatenate strings in Oracle. Concatenating Strings Two strings can be concatenated (added one after another) to form a single string using thestring concatenation operator, which is || (two pipe symbols).The following example concatenates name of the faculty with qualification. We also put twospaces between these two values to provide required space. select name || ' ' || qual from facultyNAME||''||QUAL--------------------------------------------------------George Koch MS Computer ScienceDan Appleman CS and EE graduateHerbert Schildt MS Computer ScienceDavid Hunter MS ElectronicsStephen Walther Ph.D. in PhilosophyKevin Loney MS EletronicsJamie Jaworski Bachlors of ElectricalJason Couchman OCP DBA  Oracle For Beginners Page : 2  srikanthtechnologies.com Table 1 shows the list of string functions. These functions generally take a string asparameter and also return a string as return value. Function DescriptionLENGTH (string) Returns the number of characters in the string . LOWER (string) Returns the string after converting the string tolowercase. UPPER (string) Returns the string after converting the string touppercase. INITCAP (string) Converts first character of every word touppercase and remaining to lower case. LPAD (string, length [, fillstring]) Makes the string of the given length by paddingthe string on the left either with space or with fillstring . RPAD (string, length [, fillstring]) Same as LPAD but pads on the right. LTRIM (string [, charset]) Removes all left most characters of string up tothe first character that is not in the charset  . if  charset  is not given then it defaults to blank. RTRIM (string [, charset]) Same as LTRIM, but trims on the right. TRIM (string) Trims space on both sides. SUBSTR (string, pos , length) Extracts length number of characters fromposition  pos in the string. If  length is not giventhen extracts everything from  pos . INSTR (s1,s2 [,pos [,occurrence]]) Finds the starting position of  s2 in s1 . If  occurrence is not given then it finds firstoccurrence. Search starts at  pos , if given,otherwise at the first character in s1 . ASCII (string) Returns ASCII code of the first character in thegiven string CHR (number) Returns ASCII character for the given ASCIIcode. TRANSLATE (string, from, to) Replaces characters in from with to in string. REPLACE (string, source, replace) Replaces source in string with replace . Table 1: String functions. Converting Case Functions LOWER and UPPER are straightforward. And they play a very important role in stringcomparison. As string comparison is case sensitive, LOWER or UPPER can be used to convertstrings to uniform case before they are compared.The following query tries to retrieve details of courses related to programming.  Oracle For Beginners Page : 3  srikanthtechnologies.com select name,duration from courseswhere name like '%programming%'NAME DURATION------------------------------ ---------C programming 20 The above query retrieves only one two whereas there are two rows that contain the wordprogramming. It is because of the difference in the case. So the following query is convertingthe name to lowercase before comparison. select name,duration from courseswhere LOWER(name) like '%programming%'NAME DURATION------------------------------ ---------C programming 20XML Programming 15 As NAME is converted to lowercase during comparison and compared with programming,which is in lowercase, the difference in case is ignored. The same result can be achieved evenby using UPPER function. But in that case the string must be given in uppercase –PROGRAMMING.INITCAP converts first letter of each word to capital and remaining letters to lowercase. select initcap('this IS to Test INITCAP') Resultfrom dual;RESULT-----------------------This Is To Test Initcap INSTR function INSTR returns the position in the first string where the second string starts in the first string.If second string is not found in first string, it returns 0 .The default is to return the position of first occurrence by starting the search at the very firstcharacter in first string. However, INSTR has options using which we can specify from wherethe search should start and which occurrence is to be considered.  Oracle For Beginners Page : 4  srikanthtechnologies.com The following examples illustrate the usage of two optional parameters; start    and occurrence . select instr('How do you do','do') Postionfrom dual;POSTION---------5 Though string “do” occurs for twice, the position of first occurrence is be returned. It ispossible to specify to INSTR that it should start looking for do starting from the given positionas follows. select instr('How do you do','do',8) Postionfrom dualPOSTION---------12 It is possible to specify that the position of the specified occurrence is to be returned asfollows:select instr('How do you do','do',1,2) Postionfrom dual POSTION---------12 Note: When occurrence is specified then starting position must also be specified, as third  parameter cannot be omitted while fourth parameter is given. The following example displays the details of courses where the letter p exists in the name of the course after 6 th position. select ccode,namefrom courseswhere instr(name,'n') > 6
We Need Your Support
Thank you for visiting our website and your interest in our free products and services. We are nonprofit website to share and download documents. To the running of this website, we need your help to support us.

Thanks to everyone for your continued support.

No, Thanks