CHARACTER BUILT IN FUNCTIONS - Revision history

Bacchas at 00:36, 10 November 2017

2017-11-10T00:36:06Z

← Older revision		Revision as of 00:36, 10 November 2017
Line 37:		Line 37:
	'''RETURNS PART OF A STRING. START FROM THE 2ND POSITION AND RETURN 3 CHARACHTERS'''		'''RETURNS PART OF A STRING. START FROM THE 2ND POSITION AND RETURN 3 CHARACHTERS'''
	SUBSTR('JAMES',2,3)=AME		SUBSTR('JAMES',2,3)=AME
			==INSTR===
			'''Searches a string fr a specified set of characters or substring and then returns the first character position in which the substring is found'''
			SELECT name, INSTR(name, ',') "First comma",
			INSTR(name, ',',10) "Start read and position 10",
			INSTR(name, ',',1,2) "Second Comma",

	==DECODE()==		==DECODE()==
	'''RETURNS THE VALUE MATCHING THE CONDITION TAKE THE 20 IN THE FRONT AND RETURN THE CORRESPONDING STRING'''		'''RETURNS THE VALUE MATCHING THE CONDITION TAKE THE 20 IN THE FRONT AND RETURN THE CORRESPONDING STRING'''

Bacchas: /* LPAD and RPAD */

2017-11-09T19:10:06Z

LPAD and RPAD

← Older revision		Revision as of 19:10, 9 November 2017
Line 11:		Line 11:

	==LPAD and RPAD==		==LPAD and RPAD==
	SELECT firstname, LPAD(firstname, 12 ' '), LPAD(firstname, 12 '*')		SELECT firstname, LPAD(firstname, 12, ' '), LPAD(firstname, 12 '*')
	FROM customers		FROM customers
	WHERE firstname LIKE 'J%'		WHERE firstname LIKE 'J%'

	== LTRIM and RTRIM==		== LTRIM and RTRIM==
	SELECT lastname, address, LTRIM(address, 'P.O. BOX')		SELECT lastname, address, LTRIM(address, 'P.O. BOX')

Bacchas at 21:30, 6 November 2017

2017-11-06T21:30:40Z

← Older revision		Revision as of 21:30, 6 November 2017
Line 28:		Line 28:
	WHERE state ='FL';		WHERE state ='FL';

			==CONCAT==
			SELECT firstname, lastname, CONCAT('customernumber: ', customer#) "Number"
			FROM customers
			WHERE state = 'FL';

	==SUBSTR()==		==SUBSTR()==

Bacchas at 21:26, 6 November 2017

2017-11-06T21:26:13Z

← Older revision		Revision as of 21:26, 6 November 2017
Line 16:		Line 16:
	== LTRIM and RTRIM==		== LTRIM and RTRIM==
	SELECT lastname, address, LTRIM(address, 'P.O. BOX')		SELECT lastname, address, LTRIM(address, 'P.O. BOX')
			FROM customers
			WHERE state ='FL';

			== TRANSLATE==
			SELECT name, TRANSLATE(name, ',', '-'), TRANSLATE(name, ',A', '-a')
			FROM CONTACTS;

			== REPLACE==
			SELECT lastname, address, REPLACE(address, 'P.O.', 'POST OFFICE')
	FROM customers		FROM customers
	WHERE state ='FL';		WHERE state ='FL';

Bacchas at 20:51, 6 November 2017

2017-11-06T20:51:07Z

← Older revision		Revision as of 20:51, 6 November 2017
Line 14:		Line 14:
	FROM customers		FROM customers
	WHERE firstname LIKE 'J%'		WHERE firstname LIKE 'J%'
			== LTRIM and RTRIM==
			SELECT lastname, address, LTRIM(address, 'P.O. BOX')
			FROM customers
			WHERE state ='FL';


	==SUBSTR()==		==SUBSTR()==

Bacchas at 20:47, 6 November 2017

2017-11-06T20:47:52Z

← Older revision		Revision as of 20:47, 6 November 2017
Line 6:		Line 6:
	SELECT INITCAP(firstname), INITCAP(lastname)		SELECT INITCAP(firstname), INITCAP(lastname)
	FROM customers		FROM customers
	WHERE LOWER(lastname)		WHERE LOWER(lastname) = 'nelson'
	==LENGTH()==		==LENGTH()==
	LENGTH('JOHN')=4		LENGTH('JOHN')=4

			==LPAD and RPAD==
			SELECT firstname, LPAD(firstname, 12 ' '), LPAD(firstname, 12 '*')
			FROM customers
			WHERE firstname LIKE 'J%'

	==SUBSTR()==		==SUBSTR()==

Bacchas at 20:44, 6 November 2017

2017-11-06T20:44:40Z

← Older revision		Revision as of 20:44, 6 November 2017
Line 3:		Line 3:
	==LOWER()==		==LOWER()==
	LOWER('John')=john		LOWER('John')=john
			==INITCAP==
			SELECT INITCAP(firstname), INITCAP(lastname)
			FROM customers
			WHERE LOWER(lastname)
	==LENGTH()==		==LENGTH()==
	LENGTH('JOHN')=4		LENGTH('JOHN')=4

Bacchas at 19:26, 5 November 2017

2017-11-05T19:26:56Z

← Older revision		Revision as of 19:26, 5 November 2017
Line 7:		Line 7:

	==SUBSTR()==		==SUBSTR()==
	'''RETURNS PART OF A STRING'''		'''RETURNS PART OF A STRING. START FROM THE 2ND POSITION AND RETURN 3 CHARACHTERS'''
	SUBSTR('JAMES',2,3)=AME		SUBSTR('JAMES',2,3)=AME
	==DECODE()==		==DECODE()==
	'''RETURNS THE VALUE MATCHING THE CONDITION'''		'''RETURNS THE VALUE MATCHING THE CONDITION TAKE THE 20 IN THE FRONT AND RETURN THE CORRESPONDING STRING'''
	DECODE(20,10,'TEN',20,'TWENTY',30,'THIRTY') = TWENTY		DECODE(20,10,'TEN',20,'TWENTY',30,'THIRTY') = TWENTY

	[[#Select Command\|Back To Top]]-[[Main_Page\| Home]] - [[Oracle_SQL\|Category]]		[[#Select Command\|Back To Top]]-[[Main_Page\| Home]] - [[Oracle_SQL\|Category]]

Bacchas: Created page with "==UPPER()== UPPER('john') = JOHN ==LOWER()== LOWER('John')=john ==LENGTH()== LENGTH('JOHN')=4 ==SUBSTR()== '''RETURNS PART OF A STRING''' SUBSTR('JAMES',2,3)=AME ==DECODE..."

2017-11-05T19:23:03Z

Created page with "==UPPER()== UPPER('john') = JOHN ==LOWER()== LOWER('John')=john ==LENGTH()== LENGTH('JOHN')=4 ==SUBSTR()== '''RETURNS PART OF A STRING''' SUBSTR('JAMES',2,3)=AME ==DECODE..."

New page

==UPPER()==
UPPER('john') = JOHN
==LOWER()==
LOWER('John')=john
==LENGTH()==
LENGTH('JOHN')=4

==SUBSTR()==
'''RETURNS PART OF A STRING'''
SUBSTR('JAMES',2,3)=AME
==DECODE()==
'''RETURNS THE VALUE MATCHING THE CONDITION'''
DECODE(20,10,'TEN',20,'TWENTY',30,'THIRTY') = TWENTY

[[#Select Command|Back To Top]]-[[Main_Page| Home]] - [[Oracle_SQL|Category]]