รวมคำสั่งและการใช้งาน Function จัดการ String (ข้อความ อักขระ) ใน PLSQL ORACLE

SQL String Functions - ฟังก์ชั่นจัดการข้อความ ตัวอักขระ ใน SQL

จากที่ได้แนะนำบทความ รูปแบบSyntax และตัวอย่างการใช้งานคำสั่ง Oracle/PLSQL: To_Char Function และ รูปแบบSyntax และตัวอย่างการใช้งานคำสั่ง Oracle/PLSQL: To_Date Function สำหรับใช้งานบน PL/SQL ORACLE กันไปแล้ว มาวันนี้ ผมจะมาแนะนำการใช้งานการใช้งาน Function จัดการ String (ข้อความ อักขระ) ใน PLSQL ORACLE กันนะครับ

These functions can be used within sql or pl/sql statements. For example:

โค๊ดตัวอย่าง:

   SELECT UPPER(FNAME)
    FROM EMPLOYEE;
    V_UpperName := upper(v_lowername);

Functions Returning Strings

    * UPPER(string), LOWER(string), INITCAP(string) - เปลี่ยนตัวพิมพ์เล็ก พิมพ์ใหญ่
      Returns string in all upper case, all lower case, first letter of each word capitalized, respectively.

    * LPAD(string, length, pad), RPAD(string, length, pad) - ตัวข้อความ ตัดคำ
      Returns string padded on left or right to length characters using the pad string as padding. Pad may be omitted: defaults to single space.

    * LTRIM(string,trimlist), RTRIM(string,trimlist) - ลบ/ตัดข้อความที่เกิดจากการเคาะ Space ช่องว่าง
      Returns string with the leftmost or rightmost characters that match the characters in trimlist removed. Trimlist may be omitted: defaults to single space.

    * REPLACE(string,target,replacement) - แทนที่คำหรือข้อความ
      Returns string with all occurances of target replaced with replacement. If replacement is omitted, occurances are deleted.

    * SUBSTR(string,pos,len) - ตัวข้อความ ตัดคำ
      Returns the substring of string which begins at pos and is len characters long. If pos is negative, pos is counted from the end of string. The first position in string is 1 not 0.

    * TRANSLATE(string,fromlist,tolist) - แทนที่คำ ข้อความ ตัวอักษรแบบมีเงื่อนไข
      Returns string with each character in the fromlist replaced with the corresponding character in the tolist.

    * TO_CHAR(number,'format') - แปลงตัวเลขเป็นข้อความ
      formats the number according to the format string (which must be within single quotes.
      Some Numeric Formats 9990.90    show 4 digits to the left of the decimal point and 2 to the right. If the value is zero, show 0.00. If the number is too big, show '#######'
      $9999    show a '$' before each number in the column.
      999,999,999.99    use commas and decimals according to the pattern shown.
       
Character functions Returning Numeric Values

    * INSTR(string,target,start,nth) - หาตำแหน่งตัวเลขหรือตัวอักษรในข้อความที่สนใจ
      Returns the position in string, starting the search at start, of the nth occurance of target. Start and nth may be omitted and default to 1. If target is not found, 0 is returned.

    * LENGTH(string) - หาความยาวคำหรือข้อความ
      Returns length of string in characters.

Miscellaneous Useful Functions

    * NVL(value,substitute)
      if value is null, this function is equal to substitute, otherwise it is equal to value. Value can be any Oracle datatype; substitute can be a literal, another column, or an expression but must result in the same datatype as value.

โค๊ดตัวอย่าง:

-- ordinarily SUM ignores nulls:
SQL> select sum(hours) from works_on;
SUM(HOURS)
----------
       296
-- but when everything summed is null, it returns null
SQL> select sum(hours) from works_on
  2  where hours is null;
SUM(HOURS)
----------
-- to make nulls behave like 0, use NVL:
SQL> select sum(nvl(hours,0)) from works_on
  2  where hours is null;
SUM(NVL(HOURS,0))
-----------------
                0

Checking Out Functions in SQLPlus

You can use SQLplus to see what the output from a function will be. The following use the tiny table named DUAL which has one row and one column and exists just for this purpose.

โค๊ดตัวอย่าง:

SQL> select to_char(0,'990.90') from dual;
TO_CHAR
-------
   0.00
SQL> select to_char(123456789,'990.90') from dual;
TO_CHAR
-------
#######

เป็นยังไงกันบ้างครับกับบทความรวมคำสั่งและการใช้งาน Function จัดการ String (ข้อความ อักขระ) ใน PLSQL ORACLE ลองนำไปประยุกต์ใช้กันดูนะครับ ติดปัญหาหรือสงสัย โพสต์คอมเม้นท์ สอบถามได้ที่ใต้โพสต์นี้ได้เลยครับ :)

รูปแบบSyntax และตัวอย่างการใช้งานคำสั่ง Oracle/PLSQL: To_Char Function

จากบทความรูปแบบSyntax และตัวอย่างการใช้งานคำสั่ง Oracle/PLSQL: To_Date Function ที่เคยแนะนำรูปแบบและการใช้งานฟังก์ชั่น To_Date บน Oracle/PLSQL ไปแล้ว คราวนี้มาดูรูปแบบSyntax และตัวอย่างการใช้งานคำสั่งฟังก์ชั่น To_Char บน Oracle/PLSQL กันต่อนะครับ

In Oracle/PLSQL, the to_char function converts a number or date to a string.

The syntax for the to_char function is:

    to_char( value, [ format_mask ], [ nls_language ] )

value can either be a number or date that will be converted to a string.

format_mask is optional. This is the format that will be used to convert value to a string.

nls_language is optional. This is the nls language used to convert value to a string.

Applies To(ใช้ได้กับ):

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


Examples(ตัวอย่าง) - Numbers

The following are number examples for the to_char function.

    to_char(1210.73, '9999.9') would return '1210.7'
    to_char(1210.73, '9,999.99') would return '1,210.73'
    to_char(1210.73, '$9,999.00') would return '$1,210.73'
    to_char(21, '000099') would return '000021'

Examples(ตัวอย่าง) - Dates

The following is a list of valid parameters when the to_char function is used to convert a date to a string. These parameters can be used in many combinations.

    Parameter    Explanation
    YEAR    Year, spelled out
    YYYY    4-digit year
    YYY
    YY
    Y    Last 3, 2, or 1 digit(s) of year.
    IYY
    IY
    I    Last 3, 2, or 1 digit(s) of ISO year.
    IYYY    4-digit year based on the ISO standard
    Q    Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
    MM    Month (01-12; JAN = 01).
    MON    Abbreviated name of month.
    MONTH    Name of month, padded with blanks to length of 9 characters.
    RM    Roman numeral month (I-XII; JAN = I).
    WW    Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
    W    Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
    IW    Week of year (1-52 or 1-53) based on the ISO standard.
    D    Day of week (1-7).
    DAY    Name of day.
    DD    Day of month (1-31).
    DDD    Day of year (1-366).
    DY    Abbreviated name of day.
    J    Julian day; the number of days since January 1, 4712 BC.
    HH    Hour of day (1-12).
    HH12    Hour of day (1-12).
    HH24    Hour of day (0-23).
    MI    Minute (0-59).
    SS    Second (0-59).
    SSSSS    Seconds past midnight (0-86399).
    FF    Fractional seconds.


The following are date examples for the to_char function.

    to_char(sysdate, 'yyyy/mm/dd'); would return '2003/07/09'
    to_char(sysdate, 'Month DD, YYYY'); would return 'July 09, 2003'
    to_char(sysdate, 'FMMonth DD, YYYY'); would return 'July 9, 2003'
    to_char(sysdate, 'MON DDth, YYYY'); would return 'JUL 09TH, 2003'
    to_char(sysdate, 'FMMON DDth, YYYY'); would return 'JUL 9TH, 2003'
    to_char(sysdate, 'FMMon ddth, YYYY'); would return 'Jul 9th, 2003'

You will notice that in some examples, the format_mask parameter begins with "FM". This means that zeros and blanks are suppressed. This can be seen in the examples below.

    to_char(sysdate, 'FMMonth DD, YYYY'); would return 'July 9, 2003'
    to_char(sysdate, 'FMMON DDth, YYYY'); would return 'JUL 9TH, 2003'
    to_char(sysdate, 'FMMon ddth, YYYY'); would return 'Jul 9th, 2003'

The zeros have been suppressed so that the day component shows as "9" as opposed to "09".

Frequently Asked Questions

Question:  Why doesn't this sort the days of the week in order?

    select ename, hiredate, to_char((hiredate),'fmDay') "Day"
    from emp
    order by "Day";

Answer:

In the above SQL, the fmDay format mask used in the to_char function will return the name of the Day and not the numeric value of the day.

To sort the days of the week in order, you need to return the numeric value of the day by using the fmD format mask as follows:

    select ename, hiredate, to_char((hiredate),'fmD') "Day"
    from emp
    order by "Day";

ยังไงลองนำรูปแบบSyntax และตัวอย่างการใช้งานคำสั่ง Oracle/PLSQL: To_Char Function ไปประยุกต์ใช้กันดูนะครับ ติกปัญหาหรือสงสัยอะไรโพสต์ถามได้เลยครับ :)

รูปแบบSyntax และตัวอย่างการใช้งานคำสั่ง Oracle/PLSQL: To_Date Function

In Oracle/PLSQL, the to_date function converts a string to a date.

The syntax for the to_date function is:

    to_date( string1, [ format_mask ], [ nls_language ] )

string1 is the string that will be converted to a date.

format_mask is optional. This is the format that will be used to convert string1 to a date.

nls_language is optional. This is the nls language used to convert string1 to a date.

The following is a list of options for the format_mask parameter. These parameters can be used in many combinations.

    Parameter    Explanation
    YEAR    Year, spelled out
    YYYY    4-digit year
    YYY
    YY
    Y    Last 3, 2, or 1 digit(s) of year.
    IYY
    IY
    I    Last 3, 2, or 1 digit(s) of ISO year.
    IYYY    4-digit year based on the ISO standard
    RRRR    Accepts a 2-digit year and returns a 4-digit year.
    A value between 0-49 will return a 20xx year.
    A value between 50-99 will return a 19xx year.
    Q    Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
    MM    Month (01-12; JAN = 01).
    MON    Abbreviated name of month.
    MONTH    Name of month, padded with blanks to length of 9 characters.
    RM    Roman numeral month (I-XII; JAN = I).
    WW    Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
    W    Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
    IW    Week of year (1-52 or 1-53) based on the ISO standard.
    D    Day of week (1-7).
    DAY    Name of day.
    DD    Day of month (1-31).
    DDD    Day of year (1-366).
    DY    Abbreviated name of day.
    J    Julian day; the number of days since January 1, 4712 BC.
    HH    Hour of day (1-12).
    HH12    Hour of day (1-12).
    HH24    Hour of day (0-23).
    MI    Minute (0-59).
    SS    Second (0-59).
    SSSSS    Seconds past midnight (0-86399).
    FF    Fractional seconds. Use a value from 1 to 9 after FF to indicate the number of digits in the fractional seconds. For example, 'FF4'.
    AM, A.M., PM, or P.M.    Meridian indicator
    AD or A.D    AD indicator
    BC or B.C.    BC indicator
    TZD    Daylight savings information. For example, 'PST'
    TZH    Time zone hour.
    TZM    Time zone minute.
    TZR    Time zone region.


Applies To(ใช้ได้กับ):

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


For example(ตัวอย่าง):

    to_date('2003/07/09', 'yyyy/mm/dd')    would return a date value of July 9, 2003.
    to_date('070903', 'MMDDYY')    would return a date value of July 9, 2003.
    to_date('20020315', 'yyyymmdd')    would return a date value of Mar 15, 2002.

ยังไงลองนำรูปแบบSyntax และตัวอย่างการใช้งานคำสั่ง Oracle/PLSQL: To_Date Function ไปประยุกต์ใช้กันดุนะครับ ติกปัญหาหรือสงสัยอะไรโพสต์ถามได้เลยครับ :)