UsefulSQL
Net Bako edited this page Jul 14, 2021
·
10 revisions
- SUBSTR(Substring, left, right)
/* if you want using left */
select SUBSTR('mystr', 1, 3) from dual;
-- result : mys
/* if you want using right */
select SUBSTR('mystr', -3) from dual;
-- result : str
/* if you want using substring */
select SUBSTR('mystr', 3, 2) from dual;
-- result : st
- replace(sourcetext, findstr, replacestr)
select REPLACE('mystr', 's', 'o') from dual;
-- result : myotr
- Like including
_
character
select * from all_tables where table_name like 'TBL\_%';
- mod
select mod(10, 7) from dual;
-- result : 3
- Find String from Procedure source
SELECT *
FROM ALL_SOURCE
WHERE TEXT LIKE '%EVENTSTRING%'
ORDER BY TYPE, NAME, LINE;
- Japanese Traditional datetime
SQL> select to_char(SYSDATE, 'EEDL', 'NLS_CALENDAR=''JAPANESE IMPERIAL''')
2 from dual;
TO_CHAR(DATE'1900-01-01','EEDL','NLS_CALEN
------------------------------------------
明治33年1月1日 月曜日
- DateDiff
select dtA - dtB as sDatediff from TableA;
SELECT TO_CHAR (ADD_MONTHS (SYSDATE, -1), 'YYYYMM') AS m1
, TO_CHAR (ADD_MONTHS (SYSDATE, -2), 'YYYYMM') AS m2
, TO_CHAR (ADD_MONTHS (SYSDATE, -3), 'YYYYMM') AS m3
FROM dual;
- Create DDL script
-- if use long when ddl script be truncated
set long 100000
select dbms_metadata.get_ddl('TABLE','TableName','Owner') from dual;
select dbms_metadata.get_ddl('Procedure','ProcedureName','Owner') from dual;
- Find locked SQL
select * from v$lock where block = 1;
- Update Join
-
https://devlights.hatenablog.com/entry/20091005/p1
-
user_tables -> all_tables のようにuserをallに変えたら異なるユーザーが作成したリソースが確認できる。
-
Table size check
SELECT OWNER ,SEGMENT_NAME ,SEGMENT_TYPE ,BYTES FROM DBA_SEGMENTS ;
-
SELECT stu_no,activity,score,
RANK() OVER (ORDER BY score) score_rank
FROM student
ORDER BY score_rank;
- split field by delimeter character
CREATE OR REPLACE FUNCTION STRTOKEN(
P_STRING VARCHAR2, P_DELIMIT VARCHAR2,
P_POS POSITIVEN := 1, P_NTH POSITIVEN := 1,
P_EOD VARCHAR2 := NULL)
RETURN VARCHAR2
IS
vStartPos PLS_INTEGER;
vEndPos PLS_INTEGER;
BEGIN
IF (P_POS = 1) THEN
vStartPos := 1;
ELSE
vStartPos := INSTR(P_STRING, P_DELIMIT, 1, P_POS - 1);
IF (vStartPos = 0) THEN
RETURN P_EOD;
END IF;
vStartPos := vStartPos + 1;
END IF;
vEndPos := INSTR(P_STRING, P_DELIMIT, vStartPos, P_NTH);
IF (vEndPos = 0) THEN
RETURN SUBSTR(P_STRING, vStartPos);
END IF;
RETURN SUBSTR(P_STRING, vStartPos, vEndPos - vStartPos);
END;
/
- Group by / ROLLUP
- Enable/disable/compile
alter trigger <triggername> disable;
alter trigger <triggername> compile;
alter trigger <triggername> enable;
- MySQL DB Link
- KB wiki is multi-lingual writing. If you want translate to your language, use google translate!
- Go to giip service Page : http://giipweb.littleworld.net
- Documentation : https://github.com/LowyShin/giip/wiki
- Sample automation scripts : https://github.com/LowyShin/giip/tree/gh-pages/giipscripts
See more : https://github.com/LowyShin/giip/wiki
- Token exchanges : https://tokenjar.io/GIIP
- Token exchanges manual : https://www.slideshare.net/LowyShin/giipentokenjario-giip-token-trade-manual-20190416-141149519
- GIIP Token Etherscan : https://etherscan.io/token/0x33be026eff080859eb9dfff6029232b094732c52
If you want get GIIP, contact us any time!
- LowyWorkEnv : https://github.com/LowyShin/Lowyworkenv/wiki
- CLoud RDBMS比較
- MachineLearning
- SQL Server
- ORACLE
- MySQL
- mariadh
- Redis with MySQL
- Mongodb
- Apache-Drill
- A5MK2-MultiDBMSTool
- BI
- Referrals
- Juliaの自動微分パッケージ Zygote の紹介
- color code table
- Canva - free Online Design tool
- vscode
- git(github)
- GoogleSheet
- UiPath(RPA)
- WinAutomation(official)
- Excel
- VNC
- VPN
- Note Tool
- Blog
- google news alert
- Online PDF to JPG
- LoadTest " putty
- Shell/bash
- crontab
- PowerShell
- Windows(DOS) batch
- Wscript/wsf
- AWS
- Azure
- File Sync(rsync, scp...)
- KnownPort(wikipedia)
- CentOS(Linux)
- Windows10
- Android