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() |
Chat ku článku
Martin Kasman, M Software
Smreková 3095/23
Email: martin@kasman.sk
Telefón: 0908 270 294