SQL LIKE – Oracle LIKE Operator – SQL Syntax Examples

Posted on Jun 13, 2009 in Technical Tips | 10 comments

The reserved word LIKE in Oracle SQL represents an operator that performs some basic matching on text.

LIKE provides a utility to conduct simple text pattern matching without the full power regular expressions, and hence, is simpler than using regular expressions.

Oracle TO_CHAR Function – SQL Syntax Examples (Most With Dates, TO_DATE)

Posted on Jun 8, 2009 in Technical Tips | 0 comments

This article provides common examples of TO_CHAR using dates.

Oracle Rename Column – alter table, rename column Example SQL Syntax

Posted on Jun 1, 2009 in Technical Tips | 5 comments

Following is simple example syntax for renaming a column to an Oracle table using “alter table” SQL:

1
2

ALTER TABLE [my_table_name]
RENAME COLUMN [current_column_name] TO [new_column_name];

A simple example would be:

1
2

ALTER TABLE employee
RENAME COLUMN f_name TO first_name;

Like renaming an Oracle table, you should be aware of any dependencies in code, etc. that reference a table column by name.

Oracle SUBSTR with INTSR Function – SQL Syntax Examples

Posted on May 28, 2009 in Technical Tips | 2 comments

Oracle SUBSTR and INSTR SQL functions are typically used together in practice for parsing a string. Following are some examples uses that will exemplify how to take advantage of their combined power.

Top Level Domain (TLD) From An Email Address

1
2
3
4
5
6
7
8

SELECT 
substr
(
  'xyz@mandsconsulting.com', 
  instr('xyz@mandsconsulting.com','@',1)+1
) 
FROM dual
--returns: mandsconsulting.com

Base URL

…

Oracle TO_DATE Function – SQL Syntax Examples

Posted on May 26, 2009 in Technical Tips | 2 comments

The Oracle TO_DATE SQL function is used to convert a TEXT or NTEXT representation of a date into an Oracle DATETIME value.

Below shows the TO_DATE function along with the arguments it takes:

1

to_date(string)

1

to_date(string, date_format)

1

to_date(string, date_format, options)

The Oracle TO_DATE function returns a DATETIME representation of the string input using the date_format input.

Oracle INSTR Function – SQL Syntax Examples

Posted on May 26, 2009 in Technical Tips | 5 comments

The Oracle INSTR SQL function is popular and performs materially the same operation as instr functions in many other programming languages.

Below shows the INSTR function along with the arguments it takes:

1

instr(string, SUBSTRING)

1

instr(string, SUBSTRING, start_position)

1

instr(string, SUBSTRING, start_position, occurrence)

The Oracle INSTR function returns the position (an integer) within string of the first character in substring that was found while using the corresponding start_position and occurrence.

Oracle Rename Table – Example Syntax and Dependencies

Posted on May 16, 2009 in Technical Tips | 0 comments

Renaming a table in Oracle is simple. Following is the generic Oracle table rename syntax:

1

ALTER TABLE current_table_name RENAME TO new_table_name;

In practice, you might realize you left off a prefix that you are using as a naming standard. Changing the table name can be accomplished with the following example syntax:

1

ALTER TABLE employee RENAME TO dw_employee;

If the table is already in use, I recommend that you review custom code, Oracle packages, stored procedures, and any other references to the table. You can use tools like TOAD to view dependencies on the table prior to renaming it. Below is a screenshot within TOAD of the “Used By” tab that can be used when your table is selected in the schema browser:

FreeQL – Free Oracle SQL Select Statements

Posted on May 12, 2009 in Offerings | 0 comments

M&S Consulting has begun a revolutionary service where we will write Oracle SQL statements for FREE. We have named this service FreeQL^TM.

Who can use FreeQL?

Anybody can request an Oracle SQL statement to be written, as complex as you desire. You must provide a valid, non-free email address that we will use to communicate with you. That means that we will not entertain requests from gmail, hotmail, yahoo, etc.

What’s the catch?

With years in the business writing high performing, very practical SQL statements for Oracle customers (many in the business intelligence space), we want to share our knowledge and get the word out about our skills, ability, and friendly business style.

You are welcome to sign up for our newsletter or make a small payment if you desire, though that is not a requirement.

I’m in. Now what?

Simply populate the FreeQL form and we will respond to you with SQL. Everyone wants something without spending money…and we’re here to give it to you!