跳到主要內容

[Oracle]Oracle SQL String Function


ASCII
Get The ASCII Value Of A Character ASCII(ch VARCHAR2 CHARACTER SET ANY_CS) RETURN PLS_INTEGER;
SELECT ASCII('A') FROM dual;
SELECT ASCII('Z') FROM dual;
SELECT ASCII('a') FROM dual;
SELECT ASCII('z') FROM dual;
SELECT ASCII(' ') FROM dual;

CASE Related Functions
Upper Case UPPER(ch VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET ch%CHARSET;
SELECT UPPER('Dan Morgan') FROM dual;
Lower Case LOWER(ch VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET ch%CHARSET;
SELECT LOWER('Dan Morgan') FROM dual;
Initial Letter Upper Case INITCAP(ch VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET ch%CHARSET;
SELECT INITCAP('DAN MORGAN') FROM dual;
NLS Upper Case NLS_UPPER()
SELECT NLS_UPPER('Dan Morgan', 'NLS_SORT = XDanish')
FROM dual;
NLS Lower Case NLS_LOWER()
SELECT NLS_LOWER('Dan Morgan', 'NLS_SORT = XFrench')
FROM dual;
NLS Initial Letter Upper Case NLS_INITCAP()
SELECT NLS_INITCAP('DAN MORGAN', 'NLS_SORT = XGerman')
FROM dual;

CHR
Character CHR(n PLS_INTEGER) RETURN VARCHAR2;
SELECT(CHR(68) || CHR(65) || CHR(78)) FROM dual;

SELECT(CHR(68) || CHR(97) || CHR(110)) FROM dual;

COALESCE

Returns the first non-null occurrence		 COALESCE(, , , ...)
CREATE TABLE test (
col1 VARCHAR2(1),
col2 VARCHAR2(1),
col3 VARCHAR2(1));

INSERT INTO test VALUES (NULL, 'B', 'C');
INSERT INTO test VALUES ('A', NULL, 'C');
INSERT INTO test VALUES (NULL, NULL, 'C');
INSERT INTO test VALUES ('A', 'B', 'C');

SELECT COALESCE(col1, col2, col3) FROM test;

CONCAT
Concatenate

Overload 1

 standard.CONCAT(
lef VARCHAR2 CHARACTER SET ANY_CS,
right VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET LEFT%CHARSET;
SELECT CONCAT('Dan ', 'Morgan') FROM dual;

Concatenate

Overload 2		 CONCAT(left IN CLOB, right IN CLOB) RETURN CLOB
set serveroutput on

DECLARE
c1 CLOB := TO_CLOB('Dan ');
c2 CLOB := TO_CLOB('Morgan');
c3 CLOB;
BEGIN
SELECT CONCAT('Dan ', 'Morgan')
INTO c3
FROM dual;

dbms_output.put_line(c3);
END;
/

CONVERT
Converts From One Character Set To Another CONVERT(,,
)
SELECT CONVERT('� � � � � A B C D E','US7ASCII','WE8ISO8859P1')
FROM dual;

DUMP

Returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of a value		 DUMP( [,[,[,]]])
8 Octal
10 Decimal
16 Hexidecimal
17 Single Characters
1008 octal notation with the character set name
1010 decimal notation with the character set name
1016 hexadecimal notation with the character set name
1017 single characters with the character set name
set linesize 121
col dmp format a50

SELECT table_name, DUMP(table_name) DMP FROM user_tables;

SELECT table_name, DUMP(table_name, 16) DMP FROM user_tables;

SELECT table_name, DUMP(table_name, 16, 7, 4) DMP FROM user_tables;

INSTR
See links at page bottom

INSTRB
Location of a string, within another string, in bytes INSTRB(
STR1 VARCHAR2 CHARACTER SET ANY_CS, -- test string
STR2 VARCHAR2 CHARACTER SET STR1%CHARSET, -- string to locate
POS PLS_INTEGER := 1, -- position
NTH POSITIVE := 1) -- occurrence number
RETURN PLS_INTEGER;
SELECT INSTRB('Dan Morgan', ' ', 1, 1) FROM dual;

INSTRC
Location of a string, within another string, in Unicode complete characters INSTRC(
STR1 VARCHAR2 CHARACTER SET ANY_CS, -- test string
STR2 VARCHAR2 CHARACTER SET STR1%CHARSET, -- string to locate
POS PLS_INTEGER := 1, -- position
NTH POSITIVE := 1) -- occurrence number
RETURN PLS_INTEGER;
SELECT INSTRC('Dan Morgan', ' ', 1, 1) FROM dual;

INSTR2
Location of a string, within another string, in UCS2 code points INSTR2(
STR1 VARCHAR2 CHARACTER SET ANY_CS, -- test string
STR2 VARCHAR2 CHARACTER SET STR1%CHARSET, -- string to locate
POS PLS_INTEGER := 1, -- position
NTH POSITIVE := 1) -- occurrence number
RETURN PLS_INTEGER;
SELECT INSTR2('Dan Morgan', ' ', 1, 1) FROM dual;

INSTR4
Location of a string, within another string, in UCS4 code points INSTR4(
STR1 VARCHAR2 CHARACTER SET ANY_CS, -- test string
STR2 VARCHAR2 CHARACTER SET STR1%CHARSET, -- string to locate
POS PLS_INTEGER := 1, -- position
NTH POSITIVE := 1) -- occurrence number
RETURN PLS_INTEGER;
SELECT INSTR4('Dan Morgan', ' ', 1, 1) FROM dual;

LENGTH
String Length LENGTH()
SELECT LENGTH('Dan Morgan') FROM dual;

LENGTHB
Returns length in bytes LENGTHB()
SELECT table_name, LENGTHB(table_name) FROM user_tables;
Note: Additional forms of LENGTH (LENGTHC, LENGTH2, and LENGTH4) are also available.

LPAD
Left Pad

Overload 1		 LPAD(
str1 VARCHAR2 CHARACTER SET ANY_CS,
len PLS_INTEGER,
PAD VARCHAR2 CHARACTER SET STR1%CHARSET)
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
SELECT LPAD('Dan Morgan', 25, 'x') FROM dual;
Overload 2 LPAD(
str1 VARCHAR2 CHARACTER SET ANY_CS,
len PLS_INTEGER)
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
SELECT LPAD('Dan Morgan', 25) FROM dual;
Overload 3 LPAD(
str1 CLOB CHARACTER SET ANY_CS,
len NUMBER,
PAD CLOB CHARACTER SET STR1%CHARSET)
RETURN CLOB CHARACTER SET STR1%CHARSET;
TBD
Overload 4 LPAD(
str1 CLOB CHARACTER SET ANY_CS,
len INTEGER)
RETURN CLOB CHARACTER SET STR1%CHARSET;
TBD

LTRIM
Left Trim

Overload 1		 LTRIM(
str1 VARCHAR2 CHARACTER SET ANY_CS := ' ',
tset VARCHAR2 CHARACTER SET STR1%CHARSET)
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
SELECT '->' || LTRIM(' Dan Morgan ') || '<-' FROM dual;
Overload 2 LTRIM(
STR1 VARCHAR2 CHARACTER SET ANY_CS := ' ')
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
SELECT '->' || LTRIM('xxx Dan Morgan ') || '<-' FROM dual; SELECT '->' || LTRIM('xxxDan Morgan ', 'x') || '<-' FROM dual;

MAX
The Maximum String based on the current sort parameter MAX()
SELECT MAX(table_name)
FROM user_tables;

MIN
The Minimum String based on the current sort parameter MIN()
SELECT MIN(table_name)
FROM user_tables;

NLSSORT

Returns the string of bytes used to sort a string.

The string returned is of RAW data type		 NLSSORT(, 'NLS_SORT = );
CREATE TABLE test (name VARCHAR2(15));
INSERT INTO test VALUES ('Gaardiner');
INSERT INTO test VALUES ('Gaberd');
INSERT INTO test VALUES ('G槆erd');
COMMIT;

SELECT * FROM test ORDER BY name;

SELECT * FROM test
ORDER BY NLSSORT(name, 'NLS_SORT = XDanish');

SELECT * FROM test
ORDER BY NLSSORT(name, 'NLS_SORT = BINARY_CI');

Quote Delimiters

q used to define a quote delimiter for PL/SQL		 q'';
set serveroutput on

DECLARE
s1 VARCHAR2(20);
s2 VARCHAR2(20);
s3 VARCHAR2(20);
BEGIN
s1 := q'[Isn't this cool]';
s2 := q'"Isn't this cool"';
s3 := q'|Isn't this cool|';

dbms_output.put_line(s1);
dbms_output.put_line(s2);
dbms_output.put_line(s3);
END;
/

REPLACE
See links at page bottom

REVERSE
Reverse REVERSE()
SELECT REVERSE('Dan Morgan') FROM dual;

SELECT DUMP('Dan Morgan') FROM dual;
SELECT DUMP(REVERSE('Dan Morgan')) FROM dual;

RPAD
Right Pad

Overload 1		 RPAD(str1 VARCHAR2 CHARACTER SET ANY_CS, len PLS_INTEGER,
pad VARCHAR2 CHARACTER SET STR1%CHARSET)
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
SELECT RPAD('Dan Morgan', 25, 'x') FROM dual;
Overload 2 RPAD(str1 VARCHAR2 CHARACTER SET ANY_CS, len PLS_INTEGER)
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
SELECT RPAD('Dan Morgan', 25) ||'<-' FROM dual;

RTRIM
Right Trim

Overload 1		 RTRIM(
str1 VARCHAR2 CHARACTER SET ANY_CS := ' ',
tset VARCHAR2 CHARACTER SET STR1%CHARSET)
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
SELECT '->' || RTRIM(' Dan Morganxxx') || '<-' FROM dual; SELECT '->' || RTRIM(' Dan Morganxxx', 'xxx') || '<-' FROM dual;
Overload 2 RTRIM(
str1 VARCHAR2 CHARACTER SET ANY_CS := ' ')
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
SELECT '->' || RTRIM(' Dan Morgan ') || '<-' FROM dual;

SOUNDEX

Returns Character String Containing The Phonetic Representation Of Another String		 Rules:
  • Retain the first letter of the string and remove all other occurrences of the following letters: a, e, h, i, o, u, w, y
  • Assign numbers to the remaining letters (after the first) as
    follows:
    b, f, p, v = 1
    c, g, j, k, q, s, x, z = 2
    d, t = 3
    l = 4
    m, n = 5
    r = 6
  • If two or more letters with the same number were adjacent in the original name (before step 1), or adjacent except for any intervening h and w, then omit all but the first.
  • Return the first four bytes padded with 0.

SOUNDEX(ch VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET ch%CHARSET;
CREATE TABLE test (
namecol VARCHAR2(15));

INSERT INTO test (namecol) VALUES ('Smith');
INSERT INTO test (namecol) VALUES ('Smyth');
INSERT INTO test (namecol) VALUES ('Smythe');
INSERT INTO test (namecol) VALUES ('Smither');
INSERT INTO test (namecol) VALUES ('Smidt');
INSERT INTO test (namecol) VALUES ('Smick');
INSERT INTO test (namecol) VALUES ('Smiff');
COMMIT;

SELECT name, SOUNDEX(namecol) FROM test;
-- Thanks Frank van Bortel for the idea for the above

SELECT *
FROM test
WHERE SOUNDEX(namecol) = SOUNDEX('SMITH');

SUBSTR
See links at page bottom

SUBSTRB
Returns a substring counting bytes rather than characters SUBSTRB(
STR1 VARCHAR2 CHARACTER SET ANY_CS,
POS PLS_INTEGER, -- starting position
LEN PLS_INTEGER := 2147483647) -- number of characters
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
See Demos on the Substring Page

SUBSTRC
Returns a substring within another string, using Unicode code points SUBSTRC(
STR1 VARCHAR2 CHARACTER SET ANY_CS,
POS PLS_INTEGER, -- starting position
LEN PLS_INTEGER := 2147483647) -- number of characters
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
See Demos on the Substring Page

SUBSTR2
Returns a substring within another string, using UCS2 code points SUBSTR2(
STR1 VARCHAR2 CHARACTER SET ANY_CS,
POS PLS_INTEGER, -- starting position
LEN PLS_INTEGER := 2147483647) -- number of characters
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
See Demos on the Substring Page

SUBSTR4
Returns a substring within another string, using UCS4 code points SUBSTR4(
STR1 VARCHAR2 CHARACTER SET ANY_CS,
POS PLS_INTEGER, -- starting position
LEN PLS_INTEGER := 2147483647) -- number of characters
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
See Demos on the Substring Page

TRANSLATE
See links at page bottom

TREAT
Changes The Declared Type Of An Expression TREAT ( AS REF schema.type))
SELECT name, TREAT(VALUE(p) AS employee_t).salary SALARY
FROM persons p;

TRIM (variations are LTRIM and RTRIM)
Trim Spaces TRIM()
SELECT ' Dan Morgan ' FROM dual;

SELECT TRIM(' Dan Morgan ') FROM dual;
Trim Other Characters TRIM( FROM )
SELECT TRIM('D' FROM 'Dan Morgan') FROM dual;
Trim By CHR value TRIM()
SELECT ASCII(SUBSTR('Dan Morgan',1,1)) FROM dual;

SELECT TRIM(CHR(68) FROM 'Dan Morgan') FROM dual;

Vertical Bars
Also known as Pipes ||
SELECT 'Dan' || ' ' || 'Morgan' FROM dual;

with alias

SELECT 'Dan' || ' ' || 'Morgan' NAME FROM dual;
or
SELECT 'Dan' || ' ' || 'Morgan' AS NAME FROM dual;

VSIZE
Byte Size VSIZE(e IN VARCHAR2) RETURN NUMBER
SELECT VSIZE('Dan Morgan') FROM dual;



Ref:Oracle String Function
標籤：

留言

張貼留言

這個網誌中的熱門文章

[WEB]連線 HTTPS 網站發生驗證失敗導致基礎連接已關閉

某支透過 WebClient 物件去呼叫第三方API的程式，突然有天無法使用 經過測試出現下列的錯誤 基礎連接已關閉: 傳送時發生未預期的錯誤。 InnerException : 驗證失敗，因為遠端群體已經關閉傳輸資料流。 原來是第三方的服務已經不支援 TLS 1.0 我方的程式是用.net Framework 4.0開發了 得強制讓webclient改用 TLS 1.1 或 TLS 1.2 感謝黑大提供解決方法 在程式中加入 ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12  的設定就解決了這個問題 WebClient wc = new WebClient(); ServicePointManager.SecurityProtocol = SecurityProtocolType.Ssl3 | SecurityProtocolType.Tls | SecurityProtocolType.Tls11 | SecurityProtocolType.Tls12; 參考資料：暗黑執行緒
張貼留言
閱讀完整內容

[SQL] SQL依照你的排序條件 找出目前資料的前一筆與下一筆。 Find Pre and Next DataRows of current Datarow by your order condition

有時候需要用SQL找出前一筆跟後一筆資料 用SQL的TOP是沒有辦法做到 這個時候就可以這個語法 select * from ( SELECT TOP 1 * FROM [Article] where Poid<{CurrentPoid} order by CreateDate desc) t union select * from ( SELECT TOP 1 * FROM [[Article] where Poid>{CurrentPoid} order by CreateDate ASC) t2 找出目前PK id前一個與後一個的資料（依照想要排序順序） 那如果指示想要一個資料行呈現的話 可以改用下面的SQL語法 讓這兩筆資料join在同一筆 select Pre.*,Nex.* from (SELECT TOP 1 * ,1 tID FROM [dbo].[Article] where Poid<{CurrentPoid} order by CreateDate desc) Pre left join (SELECT TOP 1 * ,1 tID FROM [dbo].[Article] where Poid>{CurrentPoid} order by CreateDate ASC) Nex on Pre.tID=Nex.tID
張貼留言
閱讀完整內容

[IIS]IIS - ASP.NET 網站基本優化設定 --筆記

運行 ASP.NET 基本上都是掛載在 IIS 上面，但 IIS 預設的設定，並不適合 24 小時不中斷的營運系統。 如果沒有適當的調整，可能會造成使用者的感受不佳，而你又偏偏不會遇到。 本篇將介紹 IIS 運行 ASP.NET 網站的基本優化設定。 應用程式集區 打開 IIS 管理員，到應用程式集區，選擇網站後，開啟進階設定： 1. 一般 (General) 佇列長度 (Queue Length) 預設值是  1000 ，當封包數量在同一時間到達該指定值，之後的 Request 都會變成 HTTP Status 503 Service Unavailable。 例：當有同時間有 1001 個 Request 一起送到 IIS，第 1001 個 Request 會直接回傳 503，不會進到 ASP.NET 處理。 也不是無限大就好，也是要看伺服器等級。 假設調成 10000，也真的有同時 10000 的量，可能會演變成  CUP High  的問題。 因此，這個欄位沒有建議值，網站封包量很大才有需要調整這個欄位。 啟動模式 (Start Mode) 預設值是  OnDemand ，當網站執行回收後，會等到第一個 Request 進來，IIS 才會把網站啟動。 所以第一個連上來的使用者會等到比較久的時間，ASP.NET 初始化完成後，使用者才會得到回應。 建議設定成  AlwaysRunning ，當網站執行回收後，IIS 就會直接啟動 ASP.NET。 2. 回收 (Recycling) 固定時間間隔 (Regular Time Interval) 預設值是  1740 ，也就是每隔 29 小時 IIS 就會把該網站重啟。 很可能重啟當下使用者正在操作，對於要 24 小時不中斷的系統來說，這真的是很不妥當的事情。 如果 ASP.NET 的 Session Mode 是用 InProc，網站重啟使用者就全被登出了。 建議設定成  0 ，也就是關閉定期重啟網站的設定。 如果網站真的需要定期重啟，可以在 特定時間 (Specific Times)  設定，固定每天哪些離峰時間做重啟的動作。 3. 快速失敗保護 (Rapid-Fail Protection) Enabled 預設值是 
張貼留言
閱讀完整內容