Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

PostgreSQL Study > String Functions > Flashcards

String Functions Flashcards

1
Q

ASCII(str)

A

Returns the numeric value of the leftmost character of the string str.

Returns 0 if str is an empty string. Returns NULL if str is NULL. ASCII() works for characters with numeric values from 0 to 255.

testdb=# SELECT ASCII(‘2’);<br></br>
+———————————————————+<br></br>
| ASCII(‘2’) |<br></br>
+———————————————————+<br></br>
| 50 |<br></br>
+———————————————————+<br></br>
1 row in set (0.00 sec)<br></br>
<br></br>
testdb=# SELECT ASCII(‘dx’);<br></br>
+———————————————————+<br></br>
| ASCII(‘dx’) |<br></br>
+———————————————————+<br></br>
| 100 |<br></br>
+———————————————————+<br></br>
1 row in set (0.00 sec)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

BIT_LENGTH(str)

A

Returns the length of the string str in bits.

testdb=# SELECT BIT_LENGTH(‘text’);
+———————————————————+
| BIT_LENGTH(‘text’) |
+———————————————————+
| 32 |
+———————————————————+
1 row in set (0.00 sec)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

CHAR_LENGTH(str)

A

Returns the length of the string str, measured in characters. A multi-byte character counts as a single character. This means that for a string containing five two-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.

testdb=# SELECT CHAR_LENGTH(‘text’);
+———————————————————+
| CHAR_LENGTH(‘text’) |
+———————————————————+
| 4 |
+———————————————————+
1 row in set (0.00 sec)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

CHARACTER_LENGTH(str)

A

CHARACTER_LENGTH() is a synonym for CHAR_LENGTH().

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

CONCAT(str1,str2,…)

A

Returns the string that results from concatenating the arguments. It may have one or more arguments. If all arguments are non-binary strings, the result is a non-binary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent binary string form; if you want to avoid that, you can use an explicit type cast, as in this example −

testdb=# SELECT CONCAT(‘My’, ‘S’, ‘QL’);
+———————————————————+
| CONCAT(‘My’, ‘S’, ‘QL’) |
+———————————————————+
| MySQL |
+———————————————————+
1 row in set (0.00 sec)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

CONCAT_WS(separator,str1,str2,…)

A

CONCAT_WS() stands for Concatenate With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated. The separator can be a string, as can the rest of the arguments. If the separator is NULL, the result is NULL.

testdb=# SELECT CONCAT_WS(‘,’,’First name’,’Last Name’ );
+———————————————————+
| CONCAT_WS(‘,’,’First name’,’Last Name’ ) |
+———————————————————+
| First name, Last Name |
+———————————————————+
1 row in set (0.00 sec)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

LCASE(str)

A

LCASE() is a synonym for LOWER().

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

LEFT(str,len)

A

Returns the leftmost len characters from the string str, or NULL if any argument is NULL.

testdb=# SELECT LEFT(‘foobarbar’, 5);
+———————————————————+
| LEFT(‘foobarbar’, 5) |
+———————————————————+
| fooba |
+———————————————————+
1 row in set (0.00 sec)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

LENGTH(str)

A

Returns the length of the string str, measured in bytes. A multi-byte character counts as multiple bytes. This means that for a string containing five two-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.

testdb=# SELECT LENGTH(‘text’);
+———————————————————+
| LENGTH(‘text’) |
+———————————————————+
| 4 |
+———————————————————+
1 row in set (0.00 sec)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

LOWER(str)

A

Returns the string str with all characters changed to lowercase according to the current character set mapping.

testdb=# SELECT LOWER(‘QUADRATICALLY’);
+———————————————————+
| LOWER(‘QUADRATICALLY’) |
+———————————————————+
| quadratically |
+———————————————————+
1 row in set (0.00 sec)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

LPAD(str,len,padstr)

A

Returns the string str, left-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters.

testdb=# SELECT LPAD(‘hi’,4,’??’);
+———————————————————+
| LPAD(‘hi’,4,’??’) |
+———————————————————+
| ??hi |
+———————————————————+
1 row in set (0.00 sec)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

LTRIM(str)

A

Returns the string str with leading space characters removed.

testdb=# SELECT LTRIM(‘ barbar’);
+———————————————————+
| LTRIM(‘ barbar’) |
+———————————————————+
| barbar |
+———————————————————+
1 row in set (0.00 sec)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

MID(str,pos,len)

A

MID(str,pos,len) is a synonym for SUBSTRING(str,pos,len).

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

POSITION(substr IN str)

A

POSITION(substr IN str) is a synonym for LOCATE(substr,str).

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

QUOTE_IDENT(string text)

A

Returns the given string suitably quoted to be used as an identifier in an SQL statement string. In the function QUOTE_IDENT, Quotes are added only if necessary.

The following are the examples for all these functions
testdb=# SELECT QUOTE_IDENT(‘Foo bar’);
quote_ident
————-
“Foo bar”
(1 row)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

REGEXP_MATCHES()

A

REGEXP_MATCHES(string text, pattern text [, flags text]) function performs a pattern match of expr against pattern. Returns 1 if expr matches pat; otherwise it returns 0.

If either expr or pat is NULL, the result is NULL.
REGEXP_MATCHES is not case sensitive, except when used with binary strings.

testdb=# SELECT REGEXP_MATCHES(‘ABCDEF’,’A%C%%’);
regexp_matches
—————-
(0 rows)

17
Q

REGEXP_REPLACE()

A

REGEXP_REPLACE(string text, pattern text, replacement text [, flags text]) function replaces substring(s) matching a POSIX regular expression.

testdb=# SELECT REGEXP_REPLACE(‘Thomas’, ‘.[mN]a.’, ‘M’);
regexp_replace
—————-
ThM
(1 row)

18
Q

REGEXP_SPLIT_TO_ARRAY()

A

REGEXP_SPLIT_TO_ARRAY(string text, pattern text [, flags text ]), Split string using a POSIX regular expression as the delimiter.

testdb=# SELECT REGEXP_SPLIT_TO_ARRAY(‘hello world’, E’\s+’);
regexp_split_to_array
———————–
{hello,world}
(1 row)

19
Q

REGEXP_SPLIT_TO_TABLE()

A

REGEXP_SPLIT_TO_TABLE(string text, pattern text [, flags text]), splits string using a POSIX regular expression as the delimiter.

testdb=# SELECT REGEXP_SPLIT_TO_TABLE(‘hello world’, E’\s+’);
regexp_split_to_table
———————–
hello
world
(2 rows)

20
Q

QUOTE_LITERAL(string text)

A

In the function QUOTE_LITERAL, embedded single-quotes and backslashes are properly doubled.

If a value is passed, coerce the given value to text and then quotes it as a literal.

testdb=# SELECT QUOTE_LITERAL(E’O'Reilly’);
quote_literal
—————
‘O’‘Reilly’
(1 row)

testdb=# SELECT QUOTE_LITERAL(42.5);
quote_literal
—————
‘42.5’
(1 row)

21
Q

QUOTE_NULLABLE(value anyelement)

A

The function QUOTE_NULLABLE, coerces the given value to text and then quote it as a literal; or, if the argument is null, returns NULL.

testdb=# SELECT QUOTE_NULLABLE(42.5);
quote_nullable
—————-
‘42.5’
(1 row)

22
Q

REPEAT(str,count)

A

Returns a string consisting of the string str repeated count times. If count is less than 1, returns an empty string. Returns NULL if str or count are NULL.

testdb=# SELECT REPEAT(‘SQL’, 3);
repeat
———–
SQLSQLSQL
(1 row)

23
Q

REPLACE(str,from_str,to_str)

A

Returns the string str with all occurrences of the string from_str replaced by the string to_str. REPLACE() performs a case-sensitive match when searching for from_str.

testdb=# SELECT REPLACE(‘www.mysql.com’, ‘w’, ‘Ww’);
replace
——————
WwWwWw.mysql.com
(1 row)

24
Q

REVERSE(str)

A

Returns the string str with the order of the characters reversed.

testdb=# SELECT REVERSE(‘abcd’);
reverse
———
dcba
(1 row)

25
Q

RIGHT(str,len)

A

Returns the rightmost len characters from the string str, or NULL if any argument is NULL.

testdb=# SELECT RIGHT(‘foobarbar’, 4);
right
——-
rbar
(1 row)

26
Q

RPAD(str,len,padstr)

A

Returns the string str, right-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters.

testdb=# SELECT RPAD(‘hi’,5,’?’);
rpad
——-
hi???
(1 row)

27
Q

RTRIM(str)

A

Returns the string str with trailing space characters removed.

testdb=# SELECT RTRIM(‘barbar ‘);
rtrim
——–
barbar
(1 row)

28
Q

SUBSTRING()

– SUBSTRING(str,pos),

– SUBSTRING(str FROM pos),

– SUBSTRING(str,pos,len)

– SUBSTRING(str FROM pos FOR len)

A

The forms without a len argument return a substring from string str starting at position pos.

The forms with a len argument return a substring len characters long from string str, starting at position pos.

The forms that use FROM are standard SQL syntax. It is also possible to use a negative value for pos. In this case, the beginning of the substring is pos characters from the end of the string, rather than the beginning. A negative value may be used for pos in any of the forms of this function.

testdb=# SELECT SUBSTRING(‘Quadratically’,5);
substring
———–
ratically
(1 row)

testdb=# SELECT SUBSTRING(‘foobarbar’ FROM 4);
substring
———–
barbar
(1 row)

testdb=# SELECT SUBSTRING(‘Quadratically’,5,6);
substring
———–
ratica
(1 row)

29
Q

TRIM()

–TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)

–TRIM([remstr FROM] str)

A

Returns the string str with all remstr prefixes or suffixes removed.

If none of the specifiers BOTH, LEADING, or TRAILING is given, BOTH is assumed. remstr is optional and, if not specified, spaces are removed.

testdb=# SELECT TRIM(‘ bar ‘);
btrim
——-
bar
(1 row)

testdb=# SELECT TRIM(LEADING ‘x’ FROM ‘xxxbarxxx’);
ltrim
——–
barxxx
(1 row)

testdb=# SELECT TRIM(BOTH ‘x’ FROM ‘xxxbarxxx’);
btrim
——-
bar
(1 row)

testdb=# SELECT TRIM(TRAILING ‘xyz’ FROM ‘barxxyz’);
rtrim
——-
bar
(1 row)

30
Q

UCASE(str)

A

UCASE() is a synonym for UPPER().

31
Q

UPPER(str)

A

Returns the string str with all characters changed to uppercase according to the current character set mapping.

testdb=# SELECT UPPER(‘manisha’);
upper
———
MANISHA
(1 row)

PostgreSQL Study (2 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions