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";
|