1 / 18

Oracle SQL String Manipulation Functions Guide

Learn to manipulate characters in Oracle SQL with single row functions like CONCAT, RPAD, LPAD, TRIM, UPPER, LOWER, INITCAP, LENGTH, SUBSTR, INSTR. Examples and tips included.

borna
Télécharger la présentation

Oracle SQL String Manipulation Functions Guide

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SINGLE ROW FUNCTIONS 1. CHARACTER MANIPULATION Prof. Carmen Popescu Oracle Academy Lead Adjunct

  2. Concatenation • str1 || str2 • CONCAT(str1,str2) • To concatenate more that 2 strings: • Str1 || str2 || str4 • CONCAT(str1,CONCAT(str2,str3))

  3. RPAD, LPAD • RPAD(string,length,['set']) • LPAD(string,length,['set']) If 'set' does not appear the string will be padded with spaces.

  4. RPAD, LPAD Examples SELECT RPAD('ab',10,'<>') FROM DUAL => ab<><><><> SELECT LPAD('xyz',15,'*') FROM DUAL • ************xyz

  5. RPAD, LPAD Examples SELECT LPAD(RPAD('abc',8,'='),13,'=') FROM DUAL => =====abc===== SELECT LPAD(RPAD('abc',8,'='),13,'=') FROM DUAL => =====abc===== SELECT LPAD(RPAD('abc',8,'='),13,'=') FROM DUAL => =====abc=====

  6. LTRIM, RTRIM • Trim off unwanted characters from the left (LTRIM), right (RTRIM) • LTRIM(string,[’set’]) • LTRIM(string,[’set’]) • Set is the collection of characters you want to trim off. If no set is specified, the function trim off spaces.

  7. LTRIM, RTRIM Examples SELECT LTRIM('**==**abc**==**','*') FROM DUAL => ==**abc**==** SELECT RTRIM('**==**abc**==**','*') FROM DUAL => **==**abc**==

  8. TRIM • trim ( [leading|trailing|both  ['set'] FROM] s1)   • Trim off set from the beginning, the end or both of the string s1 • both option is Implicit • If no set is specified, the function trim off spaces.

  9. TRIM Examples SELECT TRIM(leading 'ab' from 'ababbabbabab') FROM DUAL => babababab

  10. TRIM Examples SELECT TRIM(trailing 'ab' from 'ababbabbabab') FROM DUAL => ababbabb

  11. TRIM Examples SELECT TRIM('ab' from 'ababbabbabab') FROM DUAL => babb

  12. LOWER, UPPER, INITCAP • UPPER(string) • LOWER(string) • INITCAP(string)

  13. LOWER, UPPER, INITCAP SELECT UPPER('aBcD'), LOWER('aBcD'), INITCAP('aBcD') FROM DUAL SELECT UPPER('aBcD'), LOWER('aBcD'), INITCAP('aBcD') FROM DUAL => ABCD SELECT UPPER('aBcD'), LOWER('aBcD'), INITCAP('aBcD') FROM DUAL => ABCD abcd SELECT UPPER('aBcD'), LOWER('aBcD'), INITCAP('aBcD') FROM DUAL => ABCD abcd Abcd

  14. LENGTH SELECT LENGTH('abc') FROM DUAL => 3

  15. SUBSTR SUBSTR(string,start[,count]) - If no count is specified, the function return the substring starting at position start and going to the end of the string. SELECT SUBSTR('abcdefghi',5,2) FROM DUAL • ef SELECT SUBSTR('abcdefghi',5) FROM DUAL • efghi

  16. INSTR INSTR(string,substring[,start[,n]]) • Search for the n-th occurrence of the substring in the string, starting with the start position • If the start position is not mention the function will look for the substring starting at position 1. • If n is not mention the function will look for the first occurrence of the substring in the string

  17. INSTR examples SELECT INSTR('xyzabcxabcxabcxyz','abc') from dual • 4 SELECT INSTR('xyzabcxabcxabcxyz','abc',6) from dual • 8 SELECT INSTR('xyzabcxabcxabcxyz','abc',1,2) from dual • 8 SELECT INSTR('xyzabcxabcxabcxyz','abc',5,2) from dual • 12

  18. SUBSTR, INSTR Together SELECT Author, SUBSTR(Author, INSTR(Author,',')+2 ) || ' ' || SUBSTR(Author,1, INSTR(Author,',')-1 ) FROM Magazine Results: Eminescu, MihaiMihaiEminescu Creanga, IonIonCreanga Cosbuc, GeorgeGeorgeCosbuc

More Related