1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
|
--
-- TEXT
--
SELECT text 'this is a text string' = text 'this is a text string' AS true;
true
------
t
(1 row)
SELECT text 'this is a text string' = text 'this is a text strin' AS false;
false
-------
f
(1 row)
CREATE TABLE TEXT_TBL (f1 text);
INSERT INTO TEXT_TBL VALUES ('doh!');
INSERT INTO TEXT_TBL VALUES ('hi de ho neighbor');
SELECT '' AS two, * FROM TEXT_TBL;
two | f1
-----+-------------------
| doh!
| hi de ho neighbor
(2 rows)
-- As of 8.3 we have removed most implicit casts to text, so that for example
-- this no longer works:
select length(42);
ERROR: function length(integer) does not exist
LINE 1: select length(42);
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
-- But as a special exception for usability's sake, we still allow implicit
-- casting to text in concatenations, so long as the other input is text or
-- an unknown literal. So these work:
select 'four: '::text || 2+2;
?column?
----------
four: 4
(1 row)
select 'four: ' || 2+2;
?column?
----------
four: 4
(1 row)
-- but not this:
select 3 || 4.0;
ERROR: operator does not exist: integer || numeric
LINE 1: select 3 || 4.0;
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
/*
* string functions
*/
select concat('one');
concat
--------
one
(1 row)
select concat(1,2,3,'hello',true, false, to_date('20100309','YYYYMMDD'));
concat
----------------------
123hellotf03-09-2010
(1 row)
select concat_ws('#','one');
concat_ws
-----------
one
(1 row)
select concat_ws('#',1,2,3,'hello',true, false, to_date('20100309','YYYYMMDD'));
concat_ws
----------------------------
1#2#3#hello#t#f#03-09-2010
(1 row)
select concat_ws(',',10,20,null,30);
concat_ws
-----------
10,20,30
(1 row)
select concat_ws('',10,20,null,30);
concat_ws
-----------
102030
(1 row)
select concat_ws(NULL,10,20,null,30) is null;
?column?
----------
t
(1 row)
select reverse('abcde');
reverse
---------
edcba
(1 row)
select i, left('ahoj', i), right('ahoj', i) from generate_series(-5, 5) t(i) order by i;
i | left | right
----+------+-------
-5 | |
-4 | |
-3 | a | j
-2 | ah | oj
-1 | aho | hoj
0 | |
1 | a | j
2 | ah | oj
3 | aho | hoj
4 | ahoj | ahoj
5 | ahoj | ahoj
(11 rows)
|
