기억력이 나쁜 나에게 주는 페이지

-- 값에서 검색값의 시작위치를 반환

oracle : select instr('달력1,달력2,달력3', ',', 1) from dual; ----> 4를 반환

mssql : select charindex(',', '달력1,달력2,달력3') ----> 4를 반환

​

-- 조건 비교 후 처리시

oracle : decode(조건, true값, false값)

mssql : case when 조건 then true값 else false값 end

​

-- 현재일에서 1달 뒤 구하기

oracle : SELECT ADD_MONTHS(SYSDATE, 1) FROM DUAL;

mssql : SELECT DATEADD(mm, 1, GETDATE())

​

-- 날짜로 변환

oracle : SELECT TO_DATE('202005' || '01','YYYYMMDD' ) FROM DUAL;

mssql : SELECT CAST('202005' + '01' AS DATE)

​

-- 널값시 처리

oracle : NVL(END_YN, 'N')

mssql : ISNULL(END_YN, 'N')

​

-- 사용된 바이트 수를 반환합니다

oracle : vsize(expression)

mssql : DATALENGTH ( expression )

​

-- 조건문

oracle : IF 조건1 THEN 처리1 ELSIF 조건2 THEN 처리2 ELSE 처리3 END IF;

mssql : IF 조건1 처리1 ELSE IF 조건2 처리2 ELSE 처리3

​

-- 변수에 값 할당시

oracle : param := 0;

mssql : SET @param = 0;

​

-- 문자열 자르기

oracle : substr('20200520', 1, 4) --- 2020

mssql : substring('20200520', 1, 4)

​

oracle : substr('20200520', 0, 4) --- 2020

mssql : substring('20200520', 1, 4) -- 변환 작업시 주의해서 1로 수정해야 함

​

-- 절삭하기

oracle : SELECT TRUNC(150.75, 1) FROM DUAL;

mssql : SELECT ROUND(150.75, 1, 1);

​

-- 나머지 구하기

oracle : SELECT MOD(12,5) FROM DUAL;

mssql : SELECT 12%5

​

-- 두 기간의 월 수 구하기

oracle : SELECT TRUNC(MONTHS_BETWEEN(TO_DATE('2020-06-05','RRRR-MM-DD'), TO_DATE('2020-05-01','RRRR-MM-DD'))) "month" FROM DUAL;

mssql : SELECT DATEDIFF(MONTH, CAST('2020-05-01' AS DATE), CAST('2020-06-05' AS DATE))

​

-- 마지막 일자 구하기

oracle : select TO_CHAR(LAST_DAY(sysdate), 'DD') from dual

mssql : select DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,CAST(CONVERT(CHAR(6),GETDATE(),112)+'01' AS DATE))))

​

-- 올림

oracle : select ceil(1.1) from dual; -- 2

mssql : select ceiling(1,1) -- 2

​

-- ORACLE

SELECT LEVEL_CODE,

LAG(LEVEL_CODE) OVER (ORDER BY LEVEL_CODE) UPLEVEL,

LAG(GRADE_CODE) OVER (ORDER BY LEVEL_CODE) UPGRADE

FROM HR_CODE_LEVEL

​

-- MSSQL

SELECT A.LEVEL_CODE, B.LEVEL_CODE UPLEVEL, B.GRADE_CODE UPGRADE

FROM

(SELECT ROW_NUMBER() OVER(ORDER BY LEVEL_CODE) ROWID, LEVEL_CODE, LEVEL_NAME, GRADE_CODE FROM HR_CODE_LEVEL) A

LEFT OUTER JOIN

(SELECT ROW_NUMBER() OVER(ORDER BY LEVEL_CODE) ROWID, LEVEL_CODE, LEVEL_NAME, GRADE_CODE FROM HR_CODE_LEVEL) B

ON

A.ROWID = B.ROWID+1

[출처] blog.naver.com/dbbible/221969929542

mssql  줄바꿈 치환

 REPLACE(REPLACE(CONTENT, char(13) , '' ) ,  char(10), '' ) 

* 줄바꿈, 탭문자 제거 방법(text 타입도 포함)

- 변경 스트링 함수

replace([컬럼명], [변경할 문자], [변경될 문자])

- 엔터, 탭 문자 제거 함수 활용

Tab : char(9)

Line feed: char(10)

Carriage return: char(13)

>엔터 : replace(replace([컬럼명], char(13), ''), char(10), '')

>탭 : replace([컬럼명], char(9), '')

 텍스트 타입일 경우, [replace 함수의 인수 1에 대한 인수 데이터 형식 text이(가) 잘못되었습니다.] 에러를 확인할 수 있다.

=> replace(convert(varchar(max), [컬럼명]), [변경할 문자], [변경될 문자])

TEXT 타입 컬럼명 모두 치환

replace(replace(replace(convert(varchar(max),isnull([컬럼명], '-')), char(9), ' '),char(10), ' '), char(13), '') AS [컬럼명] 

출처: https://ngio.co.kr/3253

MSSQL 에서 sp_help "테이블명" 으로 하면 테이블의 정보를 확인할 수 있으나
컬럼의 Comment까지 같이 확인은 안된다.
아래는 테이블의 컬럼Comment를 확인할 수 있는 쿼리이다.
 
SELECT A.TABLE_NAME, 
		C.VALUE AS TABLE_COMMENT,
		A.COLUMN_NAME, A.DATA_TYPE, 
		ISNULL(CAST(A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR),  
				CAST(A.NUMERIC_PRECISION AS VARCHAR) + ',' +
				CAST(A.NUMERIC_SCALE AS VARCHAR)) AS COLUMN_LENGTH,
		A.COLUMN_DEFAULT, A.IS_NULLABLE,
		B.VALUE AS COLUM_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS A LEFT OUTER JOIN
	SYS.EXTENDED_PROPERTIES B
	ON B.major_id = object_id(A.TABLE_NAME) 
	AND A.ORDINAL_POSITION = B.minor_id
	LEFT OUTER JOIN
		(SELECT object_id(objname) AS TABLE_ID,
					VALUE
		FROM ::FN_LISTEXTENDEDPROPERTY
			(NULL, 'User','dbo','table',
				NULL, NULL, NULL)
			) C
	ON object_id(A.TABLE_NAME) = C.TABLE_ID
WHERE A.TABLE_NAME = '테이블명'
ORDER BY A.TABLE_NAME, A.ORDINAL_POSITION 
 

SELECT A.COLUMN_ID,
       A.COLUMN_NAME,
       A.DATA_TYPE,
       CASE WHEN A.DATA_TYPE = 'NUMBER' THEN A.DATA_PRECISION ||','|| A.DATA_SCALE
            WHEN A.DATA_TYPE = 'DATE' THEN '7'
            ELSE TO_CHAR(DATA_LENGTH)
       END DATA_SIZE,
       B.COMMENTS COMMENTS,
       DECODE(D.CONSTRAINT_TYPE, 'P', 'PK') AS KEY_FIELD
FROM ALL_TAB_COLUMNS A INNER JOIN ALL_COL_COMMENTS B
                       ON  A.TABLE_NAME = B.TABLE_NAME
                       AND A.OWNER = B.OWNER
                       AND A.COLUMN_NAME= B.COLUMN_NAME
                     LEFT OUTER JOIN ALL_CONS_COLUMNS C
                       ON A.TABLE_NAME = C.TABLE_NAME
                       AND A.OWNER = C.OWNER
                       AND A.COLUMN_NAME = C.COLUMN_NAME
                       AND C.POSITION IS NOT NULL
                     LEFT OUTER JOIN ALL_CONSTRAINTS D
                       ON  C.TABLE_NAME = D.TABLE_NAME
                       AND C.OWNER = D.OWNER
                       AND C.CONSTRAINT_NAME  = D.CONSTRAINT_NAME
                       AND D.CONSTRAINT_TYPE = 'P'
WHERE A.TABLE_NAME = '테이블명'
ORDER BY A.COLUMN_ID

출처 : http://blog.naver.com/PostView.nhn?blogId=2zerox&logNo=90150539116
[출처] MSSQL 테이블 정보 쿼리(Comment 포함)|작성자 수정이아빠

 SELECT sc.name +'.'+ ta.name TableName
,SUM(pa.rows) RowCnt
FROM sys.tables ta
INNER JOIN sys.partitions pa
ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN sys.schemas sc
ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
GROUP BY sc.name,ta.name
ORDER BY SUM(pa.rows) DESC


mssql 테이블 목록과 각 테이블의 데이터수 쿼리

추가)
1. 테이블 목록 가져오기
 select distinct table_name from information_schema.columns

SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE 1=1
--AND TABLE_TYPE ='VIEW'
AND TABLE_TYPE ='BASE TABLE'
--AND TABLE_NAME NOT LIKE '%TEST%'
AND TABLE_SCHEMA = 'dbo'
ORDER BY TABLE_NAME 

2. 프로시져 목록 가져오기
 select distinct specific_name from INFORMATION_SCHEMA.PARAMETERS

oracle
((날짜 함수))

[ SYSDATE ]
sysdate
-현재 시스템의 날짜 데이터를 반환
select to_char(sysdate, 'yyyymmddhh24mmss') from dual

[ ADD_MONTHS ]
add_months(date, integer)
-지정한 날짜에서 해당 월에 일정한 정수 (혹은 마이너스 정수)값을 더한 날짜를 반환
select to_char(add_months(sysdate, -2), 'yyy-mm-dd') from dual

[ LAST_DAY ]
last_day(date)
-해당 월의 마지막 날짜를 반환한다

[ MONTHS_BETWEEN ]
months_between(date_1, date_2)
-date1과 date2의 사이의 기간을 월로 나타내 준다.

((변환 함수))

[ TO_CHAR ]
to_char(number, "format")
-number형 데이타 타입을 문자열로 변환

[ NVL ]
nv(ex1, ex2)
-ex1값이 null이 아니라면 ex1값의, null이라면 ex2 값을 반환

SimpleDateFormat sb = new SimpleDateFormat("MM-dd-yyyy HH:mm:ss");
sql문에서 add_day = TO_DATE('"+sb.format(new Date())+"','MM-DD-YYYY HH24:MI:SS')
또는 add_day = sysdate

mssql
((날짜 함수))

[ GETDATE ]
-getdate는 현재 시간을 표시해 준다

[ DATEADD ]
-dateadd함수는 날자에 지정한 만큼을 더한다
dateadd(날짜형식, 더할 값, 날짜)

[ DATEDIFF ]
-datediff는 두날짜 사이의 날짜 형식에 지정된 부분을 돌려준다.
datediff(날짜형식, 시작날짜, 끝날짜)

[ DATENAME ]
-datename는 지정날짜의 날자형식의 이름을 돌려준다.
datename(날짜형식, 날짜)

[ DATEPART ]
-datepart는 날짜에서 지정한 날짜형식부분만 추출해줍니다.
datepart(날짜형식, 날짜) : 주일은 일요일부터 1로 시작해서 토요일날 7로 끝나게 된다

((변환 함수))

[ CONVERT ]
-문자열 형태로 변환(varchar,datetime,number)
convert(varchar, 컬럼명)

mysql
((날짜 함수))

[ NOW() ]
[ CURRENT_TIMESTAMP ]
[ CURRENT_TIMESTAMP() ]
[ LOCALTIME ]
[ LOCALTIME() ]
[ LOCALTIMESTAMP ]
[ LOCALTIMESTAMP() ]
-현재 시간을 리턴합니다

[ SYSDATE() ]
-'YYYY-MM-DD HH:MM:SS' 또는 'YYYYMMDDHHMMSS' 형식으로 현재 날짜나 시간을 리턴한다
- SYSDATE()는 실행 시점의 시간을 표시하므로 NOW()와 차이점이 있다

[ CURDATE() ]
[ CURRENT_DATE ]
[ CURRENT_DATE() ]
-'YYYY-MM-DD' 또는 'YYYYMMDD' 형식으로 현재 날짜를 리턴합니다.

[ CURTIME() ]
[ CURRENT_TIME ]
[ CURRENT_TIME() ]
-'HH:MM:SS' 또는 'HHMMSS.uuuuuu' 형식으로 현재 시간을 리턴합니다.

[ DATE_FORMAT(date,format) ]
-date로 지정된 날짜른 format에 의해 표시합니다.

기호 설명
%a Sun, ..., Sat와 같은 요일의 약어 표시
%b Jan, ..., Dec와 같은 달이름 약어 표시
%c 1 ~ 12 사이의 월 표시
%D 1st, 2nd, ... 와 같은 형식의 달의 날짜 표시
%d 00, ..., 31 형식의 달의 날짜 표시
%e 0, ..., 31 형식의 달의 날짜 표시
%f 마이크로초 표시
%H 24시간 형식의 시간 표시(00, ..., 23)
%h, %I 12시간 형식의 시간 표시(01, ..., 12 )
%i 분 표시(00, ..., 59)
%j 년의 날짜 표시(001, ..., 366)
%k 24시간 표시(0, ..., 23)
%l 12시간 표시(1, ..., 12)
%M 달 이름 표시(Jenuary, ..., December) 3
%m 달 순서 표시(00, ..., 12)
%p AM, PM 표시
%r 12시간제 표시(HH:MM:SS AM or PM)
%S, %s 초 표시(00, ..., 59)
%T 24시간제 표시(HH:MM:SS)
%U 일요일을 시작으로 년의 몇번째 주인가를 00, ..., 53으로 표시한다.
%u 월요일을 시작으로 년의 몇번째 주인가를 00, ..., 53으로 표시한다.
%V %X와 함께 쓰여 일요일을 시작으로 년의 몇번째 주인가를 01, ..., 53으로 표시한다.
%v %x와 함께 쓰여 월요일을 시작으로 년의 몇번째 주인가를 01, ..., 53으로 표시한다.
%W 요일명 표시(Sunday, ..., Saturday)
%w 요일의 순서를 표시(0=Sunday, ..., 6=Saturday)
%X %V와 함께 쓰이며 일요일을 시작하는 주를 기준으로 YYYY 형태의 년도를 표시한다.
%x %v와 함께 쓰이며 월요일을 시작하는 주를 기준으로 YYYY 형태의 년도를 표시한다.
%Y YYYY 형식으로 년도를 표시한다.
%y YY 형식으로 년도를 표시한다.
%% % 문자를 표시한다.

[ DATEDIFF(expr1,expr2) ]
-expr1과 expr2 사이의 일 수를 리턴합니다.

[ TIMEDIFF(expr1,expr2) ]
-expr1과 expr2 사이의 시간을 time 형식으로 리턴합니다.

mysql
new date();

오라클의 sysdate 와 같은?

 [출처] http://blog.naver.com/tei2434/130108256625