SQL找出字串出現次數

1.用CONNECT BY LEVEL
   把字串切割，然後再group by
   再去看你要的字串會出現幾次

select count(*), substr('10', '20', '30', level, 1) from dual connect by level <= length('10', '20', '30')
group by substr('10', '20', '30', level, 1)
having substr('10', '20', '30', level, 1 = ',';

https://forums.oracle.com/forums/thread.jspa?threadID=2358534

2.用replace，然後再去減掉原本的字串

select length(replace('10', '20', '30'), ',', '--')) - length('10', '20', '30') from dual;

http://dbaspot.com/oracle-server/405323-counting-strings.html

第二個方法很厲害，都沒想過可以先replace 再去減掉長度
讚!

Oracle date format

參考:http://www.oradev.com/oracle_date_format.jsp

Oracle date format

With the functions to_char and to_date, a date format can be used. Example:
select to_char(sysdate,'DD/MM/YYYY HH24:MI:SS') from dual;
will return something like: 24/03/2006 14:36:43

Here is a list of all the formats that can be used:

Format mask	Description
CC	Century
SCC	Century BC prefixed with -
YYYY	Year with 4 numbers
SYYY	Year BC prefixed with -
IYYY	ISO Year with 4 numbers
YY	Year with 2 numbers
RR	Year with 2 numbers with Y2k compatibility
YEAR	Year in characters
SYEAR	Year in characters, BC prefixed with -
BC	BC/AD Indicator *
Q	Quarter in numbers (1,2,3,4)
MM	Month of year 01, 02...12
MONTH	Month in characters (i.e. January)
MON	JAN, FEB
WW	Weeknumber (i.e. 1)
W	Weeknumber of the month (i.e. 5)
IW	Weeknumber of the year in ISO standard.
DDD	Day of year in numbers (i.e. 365)
DD	Day of the month in numbers (i.e. 28)
D	Day of week in numbers(i.e. 7)
DAY	Day of the week in characters (i.e. Monday)
FMDAY	Day of the week in characters (i.e. Monday)
DY	Day of the week in short character description (i.e. SUN)
J	Julian Day (number of days since January 1 4713 BC, where January 1 4713 BC is 1 in Oracle)
HH	Hournumber of the day (1-12)
HH12	Hournumber of the day (1-12)
HH24	Hournumber of the day with 24Hours notation (0-23)
AM	AM or PM
PM	AM or PM
MI	Number of minutes (i.e. 59)
SS	Number of seconds (i.e. 59)
SSSSS	Number of seconds this day.
DS	Short date format. Depends on NLS-settings. Use only with timestamp.
DL	Long date format. Depends on NLS-settings. Use only with timestamp.
E	Abbreviated era name. Valid only for calendars: Japanese Imperial, ROC Official and Thai Buddha.. (Input-only)
EE	The full era name
FF	The fractional seconds. Use with timestamp.
FF1..FF9	The fractional seconds. Use with timestamp. The digit controls the number of decimal digits used for fractional seconds.
FM	Fill Mode: suppresses blianks in output from conversion
FX	Format Exact: requires exact pattern matching between data and format model.
IYY or IY or I	the last 3,2,1 digits of the ISO standard year. Output only
RM	The Roman numeral representation of the month (I .. XII)
RR	The last 2 digits of the year.
RRRR	The last 2 digits of the year when used for output. Accepts fout-digit years when used for input.
SCC	Century. BC dates are prefixed with a minus.
CC	Century
SP	Spelled format. Can appear of the end of a number element. The result is always in english. For example month 10 in format MMSP returns "ten"
SPTH	Spelled and ordinal format; 1 results in first.
TH	Converts a number to it's ordinal format. For example 1 becoms 1st.
TS	Short time format. Depends on NLS-settings. Use only with timestamp.
TZD	Abbreviated time zone name. ie PST.
TZH	Time zone hour displacement.
TZM	Time zone minute displacement.
TZR	Time zone region
X	Local radix character. In america this is a period (.)

[JAVA] DATE 和STRING的轉換

Calendar today = new GregorianCalendar();

DateFormate dateFormat=new SimpleDateFormat("yyyyMMdd");

date=dateFormat.format(today);


Java字串轉日期範例

1.//欲轉換的日期字串

2.String dateString = "20010-03-02 20:25:58";

3.//設定日期格式

4.SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

5.//進行轉換

6.Date date = sdf.parse(dateString);

7.System.out.println(date);

http://cooking-java.blogspot.tw/2010/03/java-string-to-date.html

Java 日期轉字串範列

1.//目前時間

2.Date date = new Date();

3.//設定日期格式

4.SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

5.//進行轉換

6.String dateString = sdf.format(date);

7.System.out.println(dateString);

http://cooking-java.blogspot.tw/2010/03/java-date-to-string.html

JAVA年要用yyyy表示


http://stackoverflow.com/questions/3583384/converting-a-calendar-object-into-a-string-in-java-with-format-yyyy-mm-dd-hhmm

http://weick.iteye.com/blog/227707

幫select的資料編號

多一個SELECT，裡面多一個ROWNUM，就可以將資料編號

SELECT * from
(
select m.*, rownum r
from maps006 m
)
where r > 49 and r < 101

參考：

http://stackoverflow.com/questions/4552769/sql-rownum-how-to-return-rows-between-a-spacific-range

列出當月日期

select trunc(sysdate , 'MM') + rownum - 1 as "月曆" from dba_objects where rownum < 31;

REGEXP切割字串

SQL> with test as 

  2  (select 'ABC,DEF,GHI,JKL,MNO' str from dual 

  3  ) 

  4  select regexp_substr (str, '[^,]+', 1, rownum) split 

  5    from test 

  6  connect by level <= length (regexp_replace (str, '[^,]+'))  + 1

  7  / 

SPLIT

---------------------------------------------------------------------

ABC

DEF

GHI

JKL

MNO

參考
http://nuijten.blogspot.tw/2009/07/splitting-comma-delimited-string-regexp.html

http://www.oradev.com/regular_expressions_sql_oracle.jsp