Matt's SQL Reference

• Data Definition Language (DDL) Statements
• Data Manipulation Language (DML) Statements
• Character Functions
  • Concatenate: ||
  • Substring: SUBSTR
  • Trim Left: LTRIM
  • Trim Right: RTRIM
  • Replace Strings: REPLACE
  • Translate Characters: TRANSLATE
  • Convert to Uppercase: UPPER
  • Convert to Lowercase: LOWER
  • String Position: POSITION
  • Advanced Position: INSTR
  • Advanced Replacement: DECODE
  • String Length: LENGTH
  • NULL Substitution: NVL
• Math Functions
• Data Dictionary Views

Character Functions

Concatenate: ||

• Syntax: STRING1 || STRING2
• Example: SELECT LAST_NAME || ', ' || FIRST_NAME
  FROM MEMBER_TABLE;
• Output:

  LAST_NAME || ', ' || FIRST_NAME
  SMITH, JOHN
  BAKER, MARY
  JONES, JANE
  BROWN, JOE
  WHITE, LARRY
  SMITH, JILL

Substring: SUBSTR

• Syntax: SUBSTR(COLUMN, starting position, length)
• Example:

  SELECT LAST_NAME, SUBSTR(FIRST_NAME,1,1)
  FROM MEMBER_TABLE;

• Output:

  LAST_NAME	SUBSTR(FIRST_NAME,1,1)
  SMITH	J
  BAKER	M
  JONES	J
  BROWN	J
  WHITE	L
  SMITH	J

Trim Left: LTRIM

• Syntax: LTRIM(targetstring, string)
• Example:

  SELECT INTEREST, LTRIM(INTEREST, 'SCUBA ')
  FROM MEMBER_TABLE;

• Output:

  INTEREST	LTRIM(INTEREST, 'SCUBA ')
  BIKING	BIKING
  COOKING	COOKING
  RUNNING	RUNNING
  SCUBA DIVING	DIVING
  RUNNING	RUNNING
  PROGRAMMING	PROGRAMMING

Trim Right: RTRIM

• Syntax: RTRIM(targetstring, string)
• Example:

  SELECT FIRST_NAME, RTRIM(FIRST_NAME, 'E')
  FROM MEMBER_TABLE;

• Output:

  FIRST_NAME	LTRIM(FIRST_NAME, 'E')
  JOHN	JOHN
  MARY	MARY
  JANE	JAN
  JOE	JO
  LARRY	LARRY
  JILL	JILL

Replace: REPLACE

• Syntax: REPLACE(COLUMN, string, string)
• Example:

  SELECT INTEREST, REPLACE(INTEREST, 'ING', 'ER')
  FROM MEMBER_TABLE;

• Output:

  INTEREST	REPLACE(INTEREST, 'ING', 'ER')
  BIKING	BIKER
  COOKING	COOKER
  RUNNING	RUNNER
  SCUBA DIVING	SCUBA DIVER
  RUNNING	RUNNER
  PROGRAMMING	PROGRAMMER

Translate: TRANSLATE

• Syntax: TRANSLATE(COLUMN, characters, characters)
• Example:

  SELECT LAST_NAME, TRANSLATE(LAST_NAME, 'IN', 'YW')
  FROM MEMBER_TABLE;

• Output:

  LAST_NAME	TRANSLATE(LAST_NAME, 'IN', 'YW')
  SMITH	SMYTH
  BAKER	BAKER
  JONES	JOWES
  BROWN	BROWW
  WHITE	WHYTE
  SMITH	SMYTH

Uppercase: UPPER

• Syntax: UPPER(string)
• Example:

  SELECT UPPER(FIRST_NAME)
  FROM MEMBER_TABLE;

• Output:

  UPPER(FIRST_NAME)
  JOHN
  MARY
  JANE
  JOE
  LARRY
  JILL

Lowercase: LOWER

• Syntax: LOWER(string)
• Example:

  SELECT LOWER(INTEREST)
  FROM MEMBER_TABLE;

• Output:

  LOWER(INTEREST)
  biking
  cooking
  running
  scuba diving
  running
  programming

String Position: POSITION

• Syntax: POSITION(COLUMN, string)
• Example:

  SELECT INTEREST, POSITION(INTEREST, 'R')
  FROM MEMBER_TABLE;

• Output:

  INTEREST	POSITION(INTEREST, 'K')
  BIKING	3
  COOKING	4
  RUNNING	0
  SCUBA DIVING	0
  RUNNING	0
  PROGRAMMING	0

Advanced Position: INSTR

• Syntax: INSTR(COLUMN, string [, starting position] [, occurrence #])
• Example:

  SELECT INTEREST, INSTR(INTEREST, 'I',1,2)
  FROM MEMBER_TABLE;

• Output:

  INTEREST	INSTR(INTEREST, 'I',1,2)
  BIKING	4
  COOKING	0
  RUNNING	0
  SCUBA DIVING	10
  RUNNING	0
  PROGRAMMING	0

Advanced Substitution: DECODE

• Syntax: DECODE(COLUMN, string, replacementstring [, string2] [, replacement2] [,default])
• Example:

  SELECT LAST_NAME, DECODE(LAST_NAME, 'SMITH', 'SMYTHE', 'JONES', 'JOHNSON', 'WILSON')
  FROM MEMBER_TABLE;

• Output:

  LAST_NAME	DECODE(LAST_NAME, 'SMITH', 'SMYTHE', 'JONES', 'JOHNSON ', 'WILSON')
  SMITH	SMYTHE
  BAKER	WILSON
  JONES	JOHNSON
  BROWN	WILSON
  WHITE	WILSON
  SMITH	SMYTHE

String Length: LENGTH

• Syntax: LENGTH(string)
• Example:

  SELECT INTEREST, LENGTH(INTEREST)
  FROM MEMBER_TABLE;

• Output:

  INTEREST	LENGTH(INTEREST)
  BIKING	6
  COOKING	7
  RUNNING	7
  SCUBA DIVING	12
  RUNNING	7
  PROGRAMMING	11

NULL Substitution: NVL

• Syntax: NVL(COLUMN, substitution)
• Example:

  SELECT FIRST_NAME, NVL(AGE, 0)
  FROM MEMBER_TABLE;

• Output:

  FIRST_NAME	NVL(AGE, 0)
  JOHN	34
  MARY	42
  JANE	29
  JOE DIVING	36
  LARRY	0
  JILL	36