Hàm Concat with || – Oracle/PLSQL


Hàm Concat with || – Oracle/PLSQL là một trong các hàm trong danh sách được cung cấp sẵn cho người sử dụng khi thao tác với hệ quản trị cơ sở dữ liệu Oracle.
Oracle-PLSQL-function tranning-2-min


Hàm Concat with || – Oracle/PLSQL – Chức năng

The Oracle/PLSQL || operator allows you to concatenate 2 or more strings together.

Hàm Concat with || – Oracle/PLSQL – Cú pháp

Cú pháp Hàm Concat with || trong Oracle/PLSQL như sau :

string1 || string2 [ || string_n ]
Tham số
string1
The first string to concatenate.
string2
The second string to concatenate.
string_n
Optional. The nth string to concatenate.

Hàm Concat with || – Oracle/PLSQL – Phạm vi

Hàm Concat with || có thể sẵn sàng sử dụng trong Oracle/PLSQL với các phiên bản Oracle như sau:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Hàm Concat with || – Oracle/PLSQL – Ví dụ

Dưới đây là các ví dụ minh họa cách sử dụng hàm Concat with || trong Oracle/PLSQL như sau:

Let’s look at some concatentaion examples and explore how to use the || operator in Oracle/PLSQL.

For example:

'Tech on' || ' the Net'
Result: 'Tech on the Net'

'a' || 'b' || 'c' || 'd'
Result: 'abcd'

Concatenate Space Characters

When you are concatenating values together, you might want to add space characters to separate your concatenated values. Otherwise, you might get a long string with the concatenated values running together. This makes it very difficult to read the results.

Let’s look at an easy example.

We can concatenate a space character using the || operator.

For example:

SELECT 'Dave' || ' ' || 'Anderson'
FROM dual;
Result: 'Dave Anderson'

In this example, we have used the || operator to add a space character between the values Dave and Anderson. This will prevent our values from being squished together.

Instead our result would appear as follows:

'Dave Anderson'

You would more commonly use the || operator to concatenate a space character when you are concatentating multiple fields together.

For example:

SELECT first_name || ' ' || last_name AS customer_name
FROM customers;

This example would return a result set with one column that consisted of the first_name and last_name fields (separated by a space) from the customers table. The column in the result set would be aliased as customer_name.

Concatenate Single Quotes

Since the || operator will concatenate string values that are enclosed in single quotes, it isn’t straight forward how to add a single quote character within the result of the concatenated string.

Let’s look at a fairly easy example that shows how to add a single quote to the resulting string using the || operator.

For example:

SELECT 'Let''s' || ' learn Oracle'
FROM dual;
Result: 'Let's learn Oracle'

Since our string values are enclosed in single quotes, we use 2 additional single quotes within the surrounding quotes to represent a single quotation mark in the resulting concatenated string.

If you wanted to separate out the single quote from the other string values, you could also rewrite this query as follows:

SELECT 'Let' || '''' || 's' || ' learn Oracle'
FROM dual;
Result: 'Let's learn Oracle'


Tham khảo các hàm built-in trong Oracle/PLSQL khác

STT Tên hàm Phạm vi Chi tiết
1 ABS Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
2 ACOS Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
3 ADD_MONTHS Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
4 ASCII Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
5 ASCIISTR Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
6 ASIN Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
7 ATAN Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
8 ATAN2 Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
9 AVG Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
10 BFILENAME Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
11 BIN_TO_NUM Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
12 BITAND Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
13 CARDINALITY Oracle 12c, Oracle 11g, Oracle 10g xem
14 CASE Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
15 CEIL Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
16 CHR Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
17 COALESCE Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
18 CONCAT Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
19 Concat with || Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
20 CORR Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
21 COS Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
22 COSH Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
23 COVAR_POP Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
24 COVAR_SAMP Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
25 CUME_DIST Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
26 CURRENT_DATE Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
27 CURRENT_TIMESTAMP Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
28 DBTIMEZONE Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
29 DECODE Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
30 DECOMPOSE Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
31 DENSE_RANK Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
32 DUMP Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
33 EMPTY_BLOB Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
34 EMPTY_CLOB Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
35 EXP Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
36 EXTRACT Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
37 FIRST_VALUE Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
38 FLOOR Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
39 FROM_TZ Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
40 GREATEST Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
41 GROUP_ID Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
42 HEXTORAW Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
43 INITCAP Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
44 INSTR Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
45 INSTR2 Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
46 INSTR4 Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
47 INSTRB Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
48 INSTRC Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
49 LAG Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
50 LAST_DAY Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
51 LAST_VALUE Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
52 LEAD Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
53 LEAST Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
54 LENGTH Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
55 LENGTH2 Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
56 LENGTH4 Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
57 LENGTHB Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
58 LENGTHC Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
59 LISTAGG Oracle 12c, Oracle 11g Release 2 xem
60 LN Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
61 LOCALTIMESTAMP Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
62 LOG Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
63 LOWER Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
64 LPAD Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
65 LTRIM Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
66 MAX Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
67 MEDIAN Oracle 12c, Oracle 11g, Oracle 10g xem
68 MIN Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
69 MONTHS_BETWEEN Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
70 NANVL Oracle 12c, Oracle 11g, Oracle 10g xem
71 NEW_TIME Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
72 NEXT_DAY Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
73 NTH_VALUE Oracle 12c, Oracle 11g Release 2 xem
74 NULLIF Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
75 NUMTODSINTERVAL Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
76 NUMTOYMINTERVAL Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
77 NVL Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
78 NVL2 Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
79 POWER Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
80 RANK Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
81 REGEXP_COUNT Oracle 12c, Oracle 11g xem
82 REGEXP_INSTR Oracle 12c, Oracle 11g, Oracle 10g xem
83 REGEXP_REPLACE Oracle 12c, Oracle 11g, Oracle 10g xem
84 REGEXP_SUBSTR Oracle 12c, Oracle 11g, Oracle 10g xem
85 REPLACE Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
86 ROUND (numbers) Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
87 ROWNUM Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
88 RPAD Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
89 RTRIM Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
90 SESSIONTIMEZONE Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
91 SIGN Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
92 SIN Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
93 SINH Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
94 SQRT Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
95 STDDEV Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
96 SUBSTR Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
97 SUM Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
98 SYSDATE Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
99 SYSTIMESTAMP Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
100 TAN Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
101 TANH Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
102 TO_CHAR Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
103 TO_CLOB Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
104 TO_DATE Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
105 TO_DSINTERVAL Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
106 TO_LOB Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
107 TO_MULTI_BYTE Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
108 TO_NCLOB Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
109 TO_NUMBER Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
110 TO_SINGLE_BYTE Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
111 TO_TIMESTAMP Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
112 TO_TIMESTAMP_TZ Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
113 TO_YMINTERVAL Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
114 TRANSLATE Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
115 TRIM Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
116 TRUNC (dates) Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
117 TRUNC (numbers) Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
118 UID Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
119 UPPER Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
120 USER Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
121 USERENV Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
122 VAR_POP Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
123 VAR_SAMP Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
124 VARIANCE Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
125 VSIZE Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem

Blog được lập ra với mục đích để chia sẻ đề thi tới toàn bộ học sinh, sinh viên ở Việt Nam. Trong quá trình đăng bài không thể tránh được những sai sót, Nếu các bạn phát hiện ra sai sót có thể comment ở bên dưới hoặc có đề hay muốn chia sẻ tới tất cả mọi người có thể gửi thông tin vào email [email protected]


Mít&Béo


Phản hồi

Phản hồi