aboutsummaryrefslogtreecommitdiff
path: root/src/test/regress/sql/strings.sql
blob: e2c86449aeffa45fedd6d28750b7690145cb4d18 (plain)
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
--
-- Test various data entry syntaxes.
--

-- SQL92 string continuation syntax
SELECT 'first line'
' - next line'
	' - third line'
	AS "Three lines to one";

-- illegal string continuation syntax
SELECT 'first line'
' - next line' /* this comment is not allowed here */
' - third line'
	AS "Illegal comment within continuation";

--
-- test conversions between various string types
--

SELECT CAST(f1 AS text) AS "text(char)" FROM CHAR_TBL;

SELECT CAST(f1 AS text) AS "text(varchar)" FROM VARCHAR_TBL;

SELECT CAST(name 'namefield' AS text) AS "text(name)";

SELECT CAST(f1 AS char) AS "char(text)" FROM TEXT_TBL;

SELECT CAST(f1 AS char) AS "char(varchar)" FROM VARCHAR_TBL;

SELECT CAST(name 'namefield' AS char) AS "char(name)";

SELECT CAST(f1 AS varchar) AS "varchar(text)" FROM TEXT_TBL;

SELECT CAST(f1 AS varchar) AS "varchar(char)" FROM CHAR_TBL;

SELECT CAST(name 'namefield' AS varchar) AS "varchar(name)";

--
-- test SQL92 string functions
--

SELECT TRIM(BOTH FROM '  bunch o blanks  ') AS "bunch o blanks";

SELECT TRIM(LEADING FROM '  bunch o blanks  ') AS "bunch o blanks  ";

SELECT TRIM(TRAILING FROM '  bunch o blanks  ') AS "  bunch o blanks";

SELECT TRIM(BOTH 'x' FROM 'xxxxxsome Xsxxxxx') AS "some Xs";

SELECT SUBSTRING('1234567890' FROM 3) AS "34567890";

SELECT SUBSTRING('1234567890' FROM 4 FOR 3) AS "456";

SELECT POSITION('4' IN '1234567890') AS "4";

SELECT POSITION(5 IN '1234567890') AS "5";

--
-- test implicit type conversion
--

SELECT 'unknown' || ' and unknown' AS "Concat unknown types";

SELECT text 'text' || ' and unknown' AS "Concat text to unknown type";

SELECT text 'text' || char ' and char' AS "Concat text to char";

SELECT text 'text' || varchar ' and varchar' AS "Concat text to varchar";