본문 바로가기

work/oracle

오라클, MSSQL 함수 비교

문자열 함수

 

Function

Oracle

SQL Server

Convert character to ASCII

ASCII

ASCII

String concatenate

CONCAT

(expression + expression)

Convert ASCII to character

CHR

CHAR

Return starting point of character in character string (from left)

INSTR

CHARINDEX

Convert characters to lowercase

LOWER

LOWER

Convert characters to uppercase

UPPER

UPPER

Pad left side of character string

LPAD

N/A

Remove leading blank spaces

LTRIM

LTRIM

Remove trailing blank spaces

RTRIM

RTRIM

Starting point of pattern in character string

INSTR

PATINDEX

Repeat character string multiple times

RPAD

REPLICATE

Phonetic representation of character string

SOUNDEX

SOUNDEX

String of repeated spaces

RPAD

SPACE

Character data converted from numeric data

TO_CHAR

STR

Substring

SUBSTR

SUBSTRING

Replace characters

REPLACE

STUFF

Capitalize first letter of each word in string

INITCAP

N/A

Translate character string

TRANSLATE

N/A

Length of character string

LENGTH

DATALENGTH or LEN

Greatest character string in list

GREATEST

N/A

Least character string in list

LEAST

N/A

Convert string if NULL

NVL

ISNULL

 

 

 

Date 함수

 

Function

Oracle

SQL Server

Date addition

(use +)

DATEADD

Date subtraction

(use -)

DATEDIFF

Last day of month

LAST_DAY

N/A

Time zone conversion

NEW_TIME

N/A

First weekday after date

NEXT_DAY

N/A

Convert date to string

TO_CHAR

DATENAME

Convert date to number

TO_NUMBER(TO_CHAR())

DATEPART

Convert string to date

TO_DATE

CAST

Get current date and time

SYSDATE

GETDATE()