6. string

[mind1949]

Intro

String operations in most RDBMSs are, arguably, needlessly painful. Fortunately, Postgres is better than most in this regard, providing strong regular expression support. This section covers basic string manipulation, use of the LIKE operator, and use of regular expressions. I also make an effort to show you some alternative approaches that work reliably in most RDBMSs. Be sure to check out Postgres' string function docs page if you're not confident about these exercises.
Anthony Molinaro's SQL Cookbook provides some excellent documentation of (difficult) cross-DBMS compliant SQL string manipulation. I'd strongly recommend his book, particularly as it's published by O'Reilly, whose ethical policy of DRM-free ebook distribution deserves rich rewards.

Q1: formate the name of members

Output the names of all members, formatted as 'Surname, Firstname'

select surname || ', ' || firstname as name
	from cd.members

Q2: find facilities by a name prefix

Find all facilities whose name begins with 'Tennis'. Retrieve all columns.

select *
	from cd.facilities
	where name like 'Tennis%'

Q3: perform a case-insensitive search

Perform a case-insensitive search to find all facilities whose name begins with 'tennis'. Retrieve all columns.

select *
	from cd.facilities
	where upper(name) like 'TENNIS%';

解析

这里用的是表达式索引

Q4: find telephone numbers with parentheses

You've noticed that the club's member table has telephone numbers with very inconsistent formatting. You'd like to find all the telephone numbers that contain parentheses, returning the member ID and telephone number sorted by member ID.

select memid, telephone
	from cd.members
	where telephone like '(%)%';

Q5: pad zip codes with leading zeroes

The zip codes in our example dataset have had leading zeroes removed from them by virtue of being stored as a numeric type. Retrieve all zip codes from the members table, padding any zip codes less than 5 characters long with leading zeroes. Order by the new zip code.

select lpad(cast(zipcode as char(5)),5, '0') zip from cd.members order by zip  

[mind1949]

string章节练习题完成

[mind1949]

加油!