SQL funkcie pre prácu s reťazcami podľa databáz

 

SQL -> Funkcie -> funkcie pre prácu s reťazcami

Prepnúť na rozdelenie podľa funkcií

Function
Standard
DB2
SQLite
MySQL
PostgreSQL
Firebird
Virtuoso
Oracle
MSSQL
Convert character x to ASCII
N/A
ASCII(x)
N/A
ASCII(x)
ASCII(x)
ASCII_CHAR(x)[1]
ASCII(x)
ASCII(x)
ASCII(x)
Convert ASCII x to character
N/A
CHR(x)
N/A
CHAR(x)
CHR(x)
ASCII_VAL(x)[1]
CHR(x)
CHR(x)
CHAR(x)
String concatenate
arg1 || arg2
arg1 || arg2 arg1 CONCAT arg2
arg1 || arg2
CONCAT (multiple arguments)
arg1 || arg2
arg1 || arg2
CONCAT(list)
arg1 || arg2 CONCAT (only 2 arguments)
arg1 + arg2










Find first occurrence of substring search in str, starting from start
POSITION(search IN str)
LOCATE(search, str[, start]) POSSTR(str, search)
N/A
POSITION(search IN str) INSTR(str, search) LOCATE(search, str[, start])
POSITION(search IN str) STRPOS(str, search)
N/A
SUBSTR
INSTR(str, search[, start])
CHARINDEX(search, str[, start])




















Find first occurrence of pattern search in string str


N/A

N/A
STRPOS[1]
INSTR
REGEXP_INSTR(str, search)
PATINDEX(search, str)
Convert x to lowercase
LOWER(x)
LOWER(x) LCASE(x)
LOWER(x)
LOWER(x) LCASE(x)
LOWER(x)
LOWER(x)
LCASE(x)
LOWER(x)
LOWER(x)










Convert x to uppercase
UPPER(x)
UPPER(x) UCASE(x)
UPPER(x)
UPPER(x) UCASE(x)
UPPER(x)
UPPER(x)
UCASE(x) UPPER(x)
UPPER(x)
UPPER(x)










Pad left side


N/A
LPAD
LPAD
LPAD[1]
N/A
LPAD
N/A
Pad right side


N/A
RPAD
RPAD
RPAD[1]
N/A
RPAD
N/A
Remove leading blank spaces from x
TRIM(LEADING [' '] FROM x)
LTRIM(x)
N/A
LTRIM
LTRIM
TRIM
LTRIM
LTRIM
LTRIM
Remove trailing blank spaces from x
TRIM(TRAILING [' '] FROM x)
RTRIM(x)
N/A
RTRIM
RTRIM
TRIM
RTRIM
RTRIM
RTRIM
Remove leading and trailing blanks from x
TRIM(BOTH [' '] FROM x) TRIM(x)
LTRIM(RTRIM(x))
N/A
TRIM
TRIM
TRIM
TRIM
TRIM
N/A










Repeat str n times

REPEAT(str, n)
N/A
REPEAT
REPEAT
STRREPEAT[1]
REPEAT
RPAD
REPLICATE
String of n spaces

SPACE(n)
N/A
SPACE(n)
N/A
RPAD[1]
SPACE(n)
RPAD
SPACE(n)
Convert number to string

CHAR(num)


CAST
CAST

TO_CHAR
STR
Substring from string str, starting from start, length of len
SUBSTRING(str FROM start [FOR len])
SUBSTR(str, len[, start])
SUBSTR
SUBSTRING SUBSTR
SUBSTRING(str FROM start [FOR len]) SUBSTR(str, start[, len])
SUBSTRING
SUBSTR
SUBSTR
SUBSTRING(str, start, length)










Replace characters

REPLACE(string, from, to)
N/A
REPLACE
REPLACE
N/A
REPACE
REPLACE
REPLACE
Capitalize first letter of each word in string x
N/A

N/A
N/A
INITCAP(x)
N/A
INITCAP(x)
INITCAP(x)
N/A
Translate string

TRANSLATE(string, to, from)
N/A
N/A
TRANSLATE
N/A

TRANSLATE
N/A
length of string x (in characters)
char_length(x) character_length(x)
length(x)
length(x)
char_length(x)
char_length(x) character_length(x)
char_length(x) character_length(x)
length(x)
length(x)
len(x)










Length of string x (in bytes)
OCTET_LENGTH(x)
LENGTH(x)
LENGTH(x)
LENGTH(x)
OCTET_LENGTH(x)
OCTET_LENGTH(x) BIT_LENGTH(x)
LENGTH(x)

DATALENGTH(x)










Greatest character string in list


MAX

GREATEST
MAX
MAX
GREATEST
N/A
Least character string in list


MIN

LEAST
MIN
MIN
LEAST
N/A
If x is NULL then return def else return x

COALESCE(x, def)

COALESCE(x, def)
COALESCE(x, def)
COALESCE(x, def)

COALESCE(x, def)
COALESCE(x, def)
Quote SQL in string x


QUOTE(x)
QUOTE(x)

N/A
QUOTE_LITERAL(x)

QUOTENAME(x, )
Soundex index of string x

SOUNDEX(x)
SOUNDEX(x)[2]
SOUNDEX(x)[3]
N/A
N/A
N/A
SOUNDEX(x)[4]
SOUNDEX(x)
Calculate MD5 hash from string x


N/A
MD5(x)
MD5(x)
N/A
N/A
N/A
HASHBYTES('MD5', x)
Calculate SHA1 hash from string x


N/A
SHA1(x)
N/A
N/A
N/A
N/A
HASHBYTES('SHA1', x)
Generate UUID
N/A


UUID()



SYS_GUID()




vytlačiť článok  hľadať súvisiace články 

Chat ku článku

Vyhľadávanie na stránke
Reklama
Náhodný obrázok
náhodný obrázok
Kontakty

Martin Kasman, M Software
Smreková 3095/23
Email: martin@kasman.sk

Telefón: 0908 270 294