1:不区分大小写的函数 collate Chinese_PRC_CI_AS
select * from J01 left join S40 on J0101=S4004 collate Chinese_PRC_CI_AS

2: 区分大小写的函数 collate Chinese_PRC_CS_AS
select A0107 from A01 left join K60 on A0101=K6007 collate Chinese_PRC_CS_AS

3: 小写转换为大写的函数 upper
select upper(J0101) from J01 where id=2049

4: 大写转换小写的函数 lower
select lower(J0101) from J01 where id=1

5:模糊查询 like
Persons 表:
Id LastName FirstName Address City
1 Adams John Oxford Street London
2 Bush George Fifth Avenue New York
3 Carter Thomas Changan Street Beijing
A: 从"Persons" 表中选取居住在以 "N" 开始的城市里的人
SELECT * FROM Persons WHERE City LIKE 'N%'
B:从 "Persons" 表中选取居住在以 "g" 结尾的城市里的人:
SELECT * FROM Persons WHERE City LIKE '%g'
C:从 "Persons" 表中选取居住在不包含 "lon" 的城市里的人:
SELECT * FROM Persons WHERE City NOT LIKE '%lon%'

posted on 2013-04-28 08:56  朱墨烂然  阅读(1084)  评论(0编辑  收藏  举报