oracle 字符串处理总结

 

oracle 字符串处理总结

 

1、NVL()函数

   

In Oracle/PLSQL, the NVL function lets you substitute a value when a null value is encountered.

The syntax for the NVL function is:

NVL( string1, replace_with )

string1 is the string to test for a null value.

replace_with is the value returned if string1 is null.

Applies To:

• Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

Example #1:

select NVL(supplier_city, 'n/a')
from suppliers;

The SQL statement above would return 'n/a' if the supplier_city field contained a null value. Otherwise, it would return the supplier_city value.

Example #2:

select supplier_id,
NVL(supplier_desc, supplier_name)
from suppliers;

This SQL statement would return the supplier_name field if the supplier_desc contained a null value. Otherwise, it would return thesupplier_desc.

Example #3:

select NVL(commission, 0)
from sales;

This SQL statement would return 0 if the commission field contained a null value. Otherwise, it would return thecommission field.

Frequently Asked Questions

---

Question:  I tried to use the NVL function through VB to access Oracle DB.

To be precise,

select NVL(DIstinct (emp_name),'AAA'),................ from.................

I got an oracle error when I use distinct clause with NVL, but when I remove distinct it works fine.

Answer:  It is possible to the use the DISTINCT clause with the NVL function. However, the DISTINCT must come before the use of the NVL function. For example:

select distinct NVL(emp_name, 'AAA')
from employees;

Hope this helps!

---

Question:  Is it possible to use the NVL function with more than one column with the same function call?  To be clear, if i need to apply this NVL function to more than one column like this:

NVL(column1;column2 ...... , here is the default value for all )

Answer:  You will need to make separate NVL function calls for each column. For example:

select NVL(table_name, 'not found'), NVL(owner, 'not found')
from all_tables;

2、NVL2()函数

   

In Oracle/PLSQL, the NVL2 function extends the functionality found in theNVL function. It lets you substitutes a value when a null value is encountered as well as when a non-null value is encountered.

The syntax for the NVL2 function is:

NVL2( string1, value_if_NOT_null, value_if_null )

string1 is the string to test for a null value.

value_if_NOT_null is the value returned if string1 is not null.

value_if_null is the value returned if string1 is null.

Applies To:

• Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

Example #1:

select NVL2(supplier_city, 'Completed', 'n/a')
from suppliers;

The SQL statement above would return 'n/a' if the supplier_city field contained a null value. Otherwise, it would return the 'Completed'.

Example #2:

select supplier_id,
NVL2(supplier_desc, supplier_name, supplier_name2)
from suppliers;

This SQL statement would return the supplier_name2 field if the supplier_desc contained a null value. Otherwise, it would return thesupplier_name field.

3、replace()函数

   

In Oracle/PLSQL, the replace function replaces a sequence of characters in a string with another set of characters.

The syntax for the replace function is:

replace( string1, string_to_replace, [ replacement_string ] )

string1 is the string to replace a sequence of characters with another set of characters.

string_to_replace is the string that will be searched for in string1.

replacement_string is optional. All occurrences of string_to_replace will be replaced withreplacement_string in string1. If the replacement_string parameter is omitted, thereplace function simply removes all occurrences of string_to_replace, and returns the resulting string.

Applies To:

• Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

For example:

replace('123123tech', '123');would return 'tech'replace('123tech123', '123');would return 'tech'replace('222tech', '2', '3');would return '333tech'replace('0000123', '0');would return '123'replace('0000123', '0', ' ');would return '    123'

4、substr()函数

   

In Oracle/PLSQL, the substr functions allows you to extract a substring from a string.

The syntax for the substr function is:

substr( string, start_position, [ length ] )

string is the source string.

start_position is the position for extraction. The first position in the string is always 1.

length is optional. It is the number of characters to extract. If this parameter is omitted,substr will return the entire string.

Note:

If start_position is 0, then substr treats start_position as 1 (ie: the first position in the string).

If start_position is a positive number, then substr starts from the beginning of the string.

If start_position is a negative number, then substr starts from the end of the string and counts backwards.

If length is a negative number, then substr will return a NULL value.

Applies To:

• Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

For example:

substr('This is a test', 6, 2)would return 'is'substr('This is a test', 6)would return 'is a test'substr('TechOnTheNet', 1, 4)would return 'Tech'substr('TechOnTheNet', -3, 3)would return 'Net'substr('TechOnTheNet', -6, 3)would return 'The'substr('TechOnTheNet', -8, 2)would return 'On'

 

5、case end语句

   

Starting in Oracle 9i, you can use the case statement within an SQL statement. It has the functionality of an IF-THEN-ELSE statement.

The syntax for the case statement is:

CASE  [ expression ]
  WHEN condition_1 THEN result_1
  WHEN condition_2 THEN result_2
  ...
  WHEN condition_n THEN result_n
  ELSE result
END

expression is optional. It is the value that you are comparing to the list of conditions. (ie: condition_1, condition_2, ... condition_n)

condition_1 to condition_n must all be the same datatype. Conditions are evaluated in the order listed. Once acondition is found to be true, the case statement will return the result and not evaluate the conditions any further.

result_1 to result_n must all be the same datatype. This is the value returned once acondition is found to be true.

Note:

If no condition is found to be true, then the case statement will return the value in the ELSE clause.

If the ELSE clause is omitted and no condition is found to be true, then thecase statement will return NULL.

You can have up to 255 comparisons in a case statement. Each WHEN ... THEN clause is considered 2 comparisons.

Applies To:

• Oracle 9i, Oracle 10g, Oracle 11g

For example:

You could use the case statement in an SQL statement as follows: (includes theexpression clause)

select table_name,
CASE owner
  WHEN 'SYS' THEN 'The owner is SYS'
  WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
  ELSE 'The owner is another value'
END
from all_tables;

Or you could write the SQL statement using the case statement like this: (omits theexpression clause)

select table_name,
CASE
  WHEN owner='SYS' THEN 'The owner is SYS'
  WHEN owner='SYSTEM' THEN 'The owner is SYSTEM'
  ELSE 'The owner is another value'
END
from all_tables;

The above two case statements are equivalent to the following IF-THEN-ELSE statement:

IF owner = 'SYS' THEN
     result := 'The owner is SYS';

ELSIF owner = 'SYSTEM' THEN
    result := 'The owner is SYSTEM'';

ELSE
    result := 'The owner is another value';

END IF;

The case statement will compare each owner value, one by one.

One thing to note is that the ELSE clause within the case statement is optional. You could have omitted it. Let's take a look at the SQL statement above with the ELSE clause omitted.

Your SQL statement would look as follows:

select table_name,
CASE owner
  WHEN 'SYS' THEN 'The owner is SYS'
  WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
END
from all_tables;

With the ELSE clause omitted, if no condition was found to be true, the case statement would return NULL.

For Example:

Here is an example that demonstrates how to use the case statement to compare different conditions:

select
CASE
  WHEN a < b THEN 'hello'
  WHEN d < e THEN 'goodbye'
END
from suppliers;

Frequently Asked Questions

---

Question:  Can you create a case statement that evaluates two different fields? I want to return a value based on the combinations in two different fields.

Answer:  Yes, below is an example of a case statement that evaluates two different fields.

select supplier_id,
CASE
  WHEN supplier_name = 'IBM' and supplier_type = 'Hardware' THEN 'North office'
  WHEN supplier_name = 'IBM' and supplier_type = 'Software' THEN 'South office'
END
from suppliers;

So if supplier_name field is IBM and the supplier_type field is Hardware, then thecase statement will return North office. If the supplier_name field isIBM and the supplier_type is Software, the case statement will returnSouth office.

5.1

case when x = y then a else b endcase when x < y then a when x = y then b else c endcase XYZ when 'foo' then 'moo' else 'bar' end

The following little SQL script demonstrates the use of CASE WHEN. 

create table test_case_when (  a varchar2(5),  b varchar2(5));insert into test_case_when values ('*','*');insert into test_case_when values ('+','+');insert into test_case_when values ('-','-');insert into test_case_when values ('.','.');select a,   case    when b = '*' then 'star'    when b = '+' then 'plus'    when b = '-' then 'minus'     else '????'  end from test_case_when;
This select statement produces the following output: 
A     CASEW----- -----*     star+     plus-     minus.     ????
drop table test_case_when;

6、trunc()时间处理函数

   

In Oracle/PLSQL, the trunc function returns a date truncated to a specific unit of measure.

The syntax for the trunc function is:

trunc ( date, [ format ] )

date is the date to truncate.

format is the unit of measure to apply for truncating. If the format parameter is omitted, thetrunc function will truncate the date to the day value, so that any hours, minutes, or seconds will be truncated off.

Below are the valid format parameters:

UnitValid format parametersYearSYYYY, YYYY, YEAR, SYEAR, YYY, YY, YISO YearIYYY, IY, IQuarterQMonthMONTH, MON, MM, RMWeekWWIWIWWWDayDDD, DD, JStart day of the weekDAY, DY, DHourHH, HH12, HH24MinuteMI

Applies To:

• Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

For example:

trunc(to_date('22-AUG-03'), 'YEAR')would return '01-JAN-03'trunc(to_date('22-AUG-03'), 'Q')would return '01-JUL-03'trunc(to_date('22-AUG-03'), 'MONTH')would return '01-AUG-03'trunc(to_date('22-AUG-03'), 'DDD')would return '22-AUG-03'trunc(to_date('22-AUG-03'), 'DAY')would return '17-AUG-03'