Concept: String Manipulation
Description: SQL has many string manipulation
functions which can be used in procedures, triggers, or sql
to transform original data.

---

	Instr(string, set, start, occurence)
		Finds the location of a character in a string.  Use
		start and occurence to determine the character offset
		to begin the search and the occurence to report
		found location on. 

	Example

	select instr('abcde','c') position from dual

	
	Position
	________
	    3
	

---

	Length(string) Length of the string

	Example

	select length('abcde') string_length from dual

	
	string_length
	_____________
	      5
	

---

	SubStr(string, start, count) Clips out a piece
		of string. 

	Example

	select substr('abcdefgh',3,3) spliced_string from dual
	
	
	spliced_string
	______________
	     cde
	

---

	Upper(string)  Converts every letter in a string
		into uppercase

	Example

	select upper('abc') ucase from dual

	
	ucase
	______________
	     ABC
	

---

	Lower(string) Converts every letter in a string
		into lower case

	Example

	select lower('ABC') lcase from dual

	
	ucase
	______________
	     abc
	

---

	|| Concatenate two string segments into one string
		segment

	Example

	select 'ab' || 'cd' || 'ef' phrase from dual

	
	phrase
	______________
	   abcdef
	

---

	LPad(string, length,'set') Left pad a string
		to length number of characters with 'set' character.

	Example

	select lpad('abc',10,'x') phrase from dual

	
	phrase
	______________
	  xxxxxxxabc
	

---

	RPad(string,length,'set' Right pad a string
		to length number of characters with 'set' character.

	Example

	select rpad('abc',10,'x') from dual

	
	phrase
	______________
	  abcxxxxxxx
	

---

	LTrim(string)Left trim any leading zeros

	Example

	select ltrim('          abc') phrase from dual

	
	phrase
	______________
	  abc
	

---

	RTrim(string)Right trim and trailing zeros

	Example

	select rtrim('abc      ') phrase from dual

	
	phrase
	______________
	  abc
	

---

	InitCap(string)Takes the initial letter of
		every word in a string and capitalizes it.

	Example

	select initcap('abc') phrase from dual

	
	phrase
	______________
	  Abc
	

---

	TO_NUMBER(string)Converts a string to a number.

	select (TO_NUMBER('10') * 5) as value from dual

	
	value
	______________
	  50
	

---

	TO_CHAR(number,format)Converts a number to a formated string.

For Number Conversion
Format Element	Sample	Meaning
9	select to_char(1234,'9999') value from dual Yields=1234	Where each 9 represents a significant digit
0	select to_char(123,'0999') value from dual Yields=0123	Leading Zeros
$	select to_char(123.1,'$999.90') value from dual Yields=0123	Adds the currency symbol
B	select to_char(023,'B99') value from dual Yields=23	Returns Blank when the integer part is zero
S	select to_char(-23,'S99') value from dual Yields=-23	+ for positive and - for negative numbers
G	select to_char(1234,'9G999') value from dual Yields=1,234	G for group separators

		 for date conversion

		select to_char(sysdate,'mm/dd/yyyy') from dual;

---

	TO_DATE(string,format)Converts a string to a date.

	SELECT TO_DATE('10-JAN-1999','DD-MON-YYYY') FROM DUAL

	SELECT TO_DATE('January 10, 1999','Month DD, YYYY') FROM DUAL

	SELECT TO_DATE('10/1/1999','MM/DD/YYYY') FROM DUAL