/* Advanced SQL/Sybase Fall 2002 */
/* String, date, convert, and null-handling functions */
/* */
/* Concatenate strings */
/* */
1> select au_fname + ' ' + au_lname
2> from authors
3> go
-------------------------------------------------------------
Abraham Bennet
Reginald Blotchet-Halls
Cheryl Carson
...
Innes del Castillo
(23 rows affected)
/* Use convert() to shorten the concatenated strength. */
/* Could have calculated the actual length by finding and */
/* adding the maximum length of data in each column. */
/* */
1> select convert(char(30), au_fname + ' ' + au_lname)
2> from authors
3> go
FullName
------------------------------
Abraham Bennet
Reginald Blotchet-Halls
Cheryl Carson
...
Innes del Castillo
(23 rows affected)
/* Date functions */
1> select getdate()
2> go
--------------------------
Sep 23 2002 6:56PM
(1 row affected)
1> select datepart(mm, '1/15/2002')
2> go
-----------
1
(1 row affected)
1> select datename(dw, '12/9/2002')
2> go
------------------------------
Monday
(1 row affected)
1> select dateadd(dd, 90, getdate())
2> go
--------------------------
Dec 22 2002 7:00PM
(1 row affected)
1> select datediff(yy, '7/4/1776', getdate())
2> go
-----------
226
(1 row affected)
/* Date formats: convert(char(n), date_expr, style) */
1> select convert(char(12), getdate(), 101)
2> go
------------
09/23/2002
(1 row affected)
1> select convert(char(12), getdate(), 8)
2> go
------------
19:15:07
(1 row affected)
1> select convert(char(24), getdate(), 109) -- not enough chars!
2> go
------------------------
Sep 23 2002 7:15:53:733
(1 row affected)
1> select convert(char(28), getdate(), 109)
2> go
----------------------------
Sep 23 2002 7:16:23:626PM
(1 row affected)
/* Show how Sybase converts 2-digit year to 4-digit year. */
1> select convert(datetime, '1/1/49')
2> go
--------------------------
Jan 1 2049 12:00AM
(1 row affected)
1> select convert(datetime, '1/1/50')
2> go
--------------------------
Jan 1 1950 12:00AM
(1 row affected)
/* Examples using isnull, coalesce, and nullif */
/* */
/* Use isnull(price, 0) to show null prices as 0, e.g., for */
/* doing calculations. */
/* */
1> select price from titles -- as is
2> go
price
------------------------
19.99
11.95
2.99
19.99
19.99
2.99
NULL
22.95
20.00
NULL
21.59
...
(18 rows affected)
1> select isnull(price, 0) NoNulls -- showing nulls as 0
2> from titles
3> go
NoNulls
------------------------
19.99
11.95
2.99
19.99
19.99
2.99
0.00
22.95
20.00
0.00
21.59
...
(18 rows affected)
/* Use coalesce(price, 0) to achieve same result as above. */
/* */
1> select coalesce(price, 0) NoNulls
2> from titles
3> go
NoNulls
------------------------
19.99
11.95
2.99
19.99
19.99
2.99
0.00
22.95
20.00
0.00
21.59
...
(18 rows affected)
/* Another example with coalesce, setting to a different value. */
/* */
1> select advance from titles
2> go
advance
------------------------
5,000.00
5,000.00
10,125.00
5,000.00
0.00
15,000.00
NULL
7,000.00
8,000.00
NULL
7,000.00
...
(18 rows affected)
1> select coalesce(advance, 3333) from titles
2> go
------------------------
5,000.00
5,000.00
10,125.00
5,000.00
0.00
15,000.00
3,333.00
7,000.00
8,000.00
3,333.00
7,000.00
...
(18 rows affected)
/* Example using coalesce with more than one column. */
/* Coalesce finds first non-null column value. */
/* */
1> use evelyn
2> go
1> select * from grades
2> go
stuid test1 test2 test3
----- ----- ----- -----
1 75 80 99
2 NULL NULL 80
(2 rows affected)
1> select stuid, coalesce(test1, test2, test3) from grades
2> go
stuid
----- ---
1 75
2 80
(2 rows affected)
/* */
/* Example of nullif: Set to null if both arguments equal. */
/* */
1> use pubs2
2> go
1> select title_id, type from titles
2> go
title_id type
-------- ------------
BU1032 business
MC3021 mod_cook
...
MC3026 UNDECIDED
...
TC4203 trad_cook
TC7777 trad_cook
(18 rows affected)
1> select title_id, nullif(type, 'UNDECIDED') from titles
2> go
title_id
-------- ------------
BU1032 business
MC3021 mod_cook
...
MC3026 NULL
...
TC4203 trad_cook
TC7777 trad_cook
(18 rows affected)