SQL数据操作基础(中级)9

SQL数据操作基础(中级)9  
   
　　转换数据

SQL Sever足够强大，可以在需要的时候把大部分数值从一种类型转换为另一种类型。例如，要比较SMALLINT型和INT型数据的大小，你不需要进行显式的类型转换。SQL Sever会为你完成这项工作。但是，当你想在字符型数据和其它类型的数据之间进行转换时，你的确需要自己进行转换操作。例如，假设你想从一个MONEY型字段中取出所有的值，并在结果后面加上字符串“US Dollars”。你需要使用函数CONVERT（），如下例所示：

SELECT CONVERT(CHAR(8),price)+’US Dollars’ FROM orders

函数CONVERT（）带有两个变量。第一个变量指定了数据类型和长度。第二个变量指定了要进行转换的字段。在这个例子中，字段price被转换成长度为8个字符的CHAR型字段。字段price要被转换成字符型，才可以在它后面连接上字符串’US Dollars’。

当向BIT型，DATETIME型，INT型，或者NUMERIC型字段添加字符串时，你需要进行同样的转换操作。例如，下面的语句在一个SELECT语句的查询结果中加入字符串’The vote is’，该SELECT语句返回一个BIT型字段的值:

SELECT ‘The vote is’+CONVERT(CHAR(1),vote) FROM opinion

下面是这个语句的结果示例：

The vote is 1

The vote is 1

The vote is 0

(3 row(s) affected)

如果你不进行显式的转换，你会收到如下的错误信息：

Implicit conversion from datatype ‘varchar’ to ‘bit’ is not allowec.

Use the CONVERT function to run this query.

操作字符串数据

SQL Sever有许多函数和表达式，使你能对字符串进行有趣的操作，包括各种各样的模式匹配和字符转换。在这一节中，你将学习如何使用最重要的字符函数和表达式。

匹配通配符

假设你想建立一个与Yahoo功能相似的Internet目录。你可以建立一个表用来保存一系列的站点名称，统一资源定位器（URL），描述，和类别，并允许访问者通过在HTML form中输入关键字来检索这些内容。

假如有一个访问者想从这个目录中得到其描述中包含关键字trading card的站点的列表。要取出正确的站点列表，你也许试图使用这样的查询：

SELECT site_name FROM site_directory WHERE site_desc=’trading card’

这个查询可以工作。但是，它只能返回那些其描述中只有trading card这个字符串的站点。例如，一个描述为We have the greatest collection of trading cards in the world!的站点不会被返回。

要把一个字符串与另一个字符串的一部分相匹配，你需要使用通配符。你使用通配符和关键字LIKE来实现模式匹配。下面的语句使用通配符和关键字LIKE重写了上面的查询，以返回所有正确站点的名字：

SELECT SITE_name FROM site_directory

WHERE site_desc LIKE ‘%trading cark%’

在这个例子中，所有其描述中包含表达式trading card的站点都被返回。描述为We have the greatest collection of trading cards in the world!的站点也被返回。当然，如果一个站点的描述中包含I am trading cardboard boxes online ，该站点的名字也被返回。

注意本例中百分号的使用。百分号是通配符的例子之一。它代表0个或多个字符。通过把trading card括在百分号中，所有其中嵌有字符串trading card的字符串都被匹配。

现在，假设你的站点目录变得太大而不能在一页中完全显示。你决定把目录分成两部分。在第一页，你想显示所有首字母在A到M之间的站点。在第二页，你想显示所有首字母在N到Z之间的站点。要得到第一页的站点列表，你可以使用如下的SQL语句：

SELECT site_name FROM site_directory WHERE site_name LIKE ‘[A-M]%’

在这个例子中使用了表达式[A-M]，只取出那些首字母在A到M之间的站点。中括号（[]）用来匹配处在指定范围内的单个字符。要得到第二页中显示的站点，应使用这个语句：

SELECT site_name FROM site_directory

WHERE site_name LIKE ‘[N-Z]%’

在这个例子中，括号中的表达式代表任何处在N到Z之间的单个字符。

假设你的站点目录变得更大了，你现在需要把目录分成更多页。如果你想显示那些以A，B或C开头的站点，你可以用下面的查询来实现：

SELECT site_name FROM site_directory WHERE site_name LIKE ‘[ABC]%’

在这个例子中，括号中的表达式不再指定一个范围，而是给出了一些字符。任何一个其名字以这些字符中的任一个开头的站点都将被返回。

通过在括号内的表达式中同时包含一个范围和一些指定的字符，你可以把这两种方法结合起来。例如，用下面的这个查询，你可以取出那些首字母在C到F之间，或者以字母Y开头的站点：

SELECT site_name FROM site_directory WHERE site_name LIKE ‘[C-FY]%’

在这个例子中，名字为Collegescape和Yahoo的站点会被选取，而名字为Magicw3的站点则不会被选取。

你也可以使用脱字符（^）来排除特定的字符。例如，要得到那些名字不以Y开头的站点，你可以使用如下的查询：

SELECT site_name FROM site_directory WHERE site_name LIKE ‘[^Y]%’

对给定的字符或字符范围均可以使用脱字符。

最后，通过使用下划线字符（_），你可以匹配任何单个字符。例如，下面这个查询返回每一个其名字的第二个字符为任何字母的站点：

SELECT site_name FROM site_directory WHERE site_name LIKE ‘M_crosoft’

这个例子既返回名为Microsoft的站点，也返回名为Macrosoft的站点。但是，名字为Moocrosoft的站点则不被返回。与通配符’％’不同，下划线只代表单个字符。

注意：

如果你想匹配百分号或下划线字符本身，你需要把它们括在方括号中。如果你想匹配连字符(-)，应把它指定为方括号中的第一个字符。如果你想匹配方括号，应把它们也括在方括号中。例如，下面的语句返回所有其描述中包含百分号的站点：

SELECT site_name FROM site_directory WHERE site_desc LIKE ‘%[%]%’

匹配发音

Microsoft SQL 有两个允许你按照发音来匹配字符串的函数。函数SOUNDEX（）给一个字符串分配一个音标码，函数DIFFERENCE（）按照发音比较两个字符串。当你不知道一个名字的确切拼写，但多少知道一点它的发音时，使用这两个函数将有助于你取出该记录。

例如，如果你建立一个Internet目录，你也许想增加一个选项，允许访问者按照站点名的发音来搜索站点，而不是按名字的拼写。考虑如下的语句：

SELECT site_name FROM site_directory

WHERE DIFFERENCE(site_name , ‘Microsoft’>3

这个语句使用函数DEFFERENCE（）来取得其名字的发音与Microsoft非常相似的站点。函数DIFFERENCE（）返回一个0到4之间的数字。如果该函数返回4，表示发音非常相近；如果该函数返回0，说明这两个字符串的发音相差很大。

例如，上面的语句将返回站点名Microsoft和Macrosoft。这两个名字的发音与Microsoft都很相似。如果你把上一语句中的大于3改为大于2，那么名为Zicrosoft和Megasoft的站点也将被返回。最后，如果你只需要差别等级大于1即可，则名为Picosoft和Minisoft的站点也将被匹配。

要深入了解函数DIFFERENCE（）是如何工作的，你可以用函数SOUNDEX（）来返回函数DIFFERENCE（）所使用的音标码。这里有一个例子：

SELECT site_name ‘site name’,SOUNDEX(site_name) ‘sounds like’

这个语句选取字段site_name的所有数据及其音标码。下面是这个查询的结果：

site name sounds like

……………………………………………………………….

Yahoo Y000

Mahoo M000

Microsoft M262

Macrosoft M262

Minisoft M521

Microshoft M262

Zicrosoft Z262

Zaposoft Z121

Millisoft M421

Nanosoft N521

Megasoft M221

Picosoft P221

(12 row(s) affected)

如果你仔细看一下音标码，你会注意到音标码的第一个字母与字段值的第一个字母相同。例如，Yahoo和Mahoo的音标码只有第一个字母不同。你还可以发现Microsoft和Macrosoft的音标码完全相同。

函数DIFFERENDE（）比较两个字符串的第一个字母和所有的辅音字母。该函数忽略任何元音字母（包括y），除非一个元音字母是一个字符串的第一个字母。

不幸的是，使用SOUNDEX（）和DIFFERENCE()有一个欠缺。WHERE子句中包含这两个函数的查询执行起来效果不好。因此，你应该小心使用这两个函数。

删除空格

有两个函数，TTRIM（）和LTRIM（），可以用来从字符串中剪掉空格。函数LTRIM（）去除应该字符串前面的所有空格；函数RTRIM（）去除一个字符串尾部的所有空格。这里有一个任何使用函数RTRIM（）的例子：

SELECT RTRIM(site_name) FROM site_directory

在这个例子中，如果任何一个站点的名字尾部有多余的空格，多余的空格将从查询结果中删去。

你可以嵌套使用这两个函数，把一个字符串前后的空格同时删去：

SELECT LTRIM(RTRIM(site_name) FROM site_directory

你会发现，在从CHAR型字段中剪掉多余的空格时，这两个函数非常有用。记住，如果你把一个字符串保存在CHAR型字段中，该字符串会被追加多余的空格，以匹配该字段的长度。用这两个函数，你可以去掉无用的空格，从而解决这个问题。