Sql2008--6

--将 AdventureWorks 数据库中Contact表的所有行都插入到名为NewContact的新表，
--并使用IDENTITY函数在NewContact表中从101而不是1开始编号
use AdventureWorks
go --首先判断该表是否存，如存在，则删除之
if OBJECT_ID (N'Person.NewContact',N'U') is not null
drop table Person.NewContact;
go --设置数据库为大容量日志恢复模式
ALTER DATABASE AdventureWorks set RECOVERY BULK_LOGGED;
select IDENTITY(smallint,101,1) as ContactNum,
FirstName as First,
LastName as Last
into Person.NewContact
from Person.Contact;
go --设置数据库为完整恢复模式
ALTER DATABASE AdventureWorks set RECOVER FULL;
go
select ContactNum,First,Last from Person.NewContact;
go
--使用 ISNULL 函数检查表达式是否为NULL
USE AdventureWorks;
GO
select AVG(ISNULL(weight,80))
from Production.Product;
go

use AdventureWorks
go
select ss.Description,ss.DiscountPct,ss.MinQty,ISNULL(ss.MaxQty,0) as 'MaxQuantity'
from Sales.SpecialOffer ss

--使用ISNUMERIC函数判断表达式是否有效的numeric类型
use AdventureWorks;
go
select pa.City,pa.PostalCode
from Person.Address pa
where ISNUMERIC(PostalCode)<>1;--使用ISNUMERIC函数来返回非数值的列
go

--字符串转换
select top 10 StateProvinceID,CountryRegionCode,Name
from Person.StateProvince

select top 10 ps.StateProvinceID,ps.CountryRegionCode,UPPER(ps.Name) as UName
from Person.StateProvince ps

select top 10 ps.StateProvinceID,ps.StateProvinceCode,LOWER(ps.Name) as UName
from Person.StateProvince ps

--去 字符串空格函数
SELECT TOP 10 PS.StateProvinceID,RTRIM(PS.CountryRegionCode)+PS.Name AS CRName
FROM Person.StateProvince PS

select top 10 AddressID,AddressLine1
from Person.Address

select top 10 AddressID, LTRIM(AddressLine1)
from Person.Address

--取字符串长度的函数

select top 10 StateProvinceID,LEN(CountryRegionCode) as CountryRegionCodelen,
LEN(Name) as Namelen
from Person.StateProvince

--查询包含部分字符串内容的数据
select top 10 pa.AddressID,pa.AddressLine1
from Person.Address pa
where RIGHT(pa.AddressLine1,5) ='Drive'

select top 10 pa.AddressID,SUBSTRING(pa.AddressLine1,1,3)
from Person.Address pa

--颠倒字符串的顺序
select top 10 ps.StateProvinceID,ps.CountryRegionCode,
REVERSE(Name) as RName
from Person.StateProvince ps
--字符串替换
select top 10 pa.AddressID,REPLACE(pa.AddressLine1,'Drive','指定地址') as 'NewAddress'
from Person.Address pa
order by AddressID