---------------------------------------------------------------- -- Author :fredrickhu(我是小F,向高手学习) -- Date :2009-11-20 13:53:54 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([省份] varchar(9)) insert [tb] select '广东/深圳' union all select '湖南/长沙' union all select '北京/北京' --------------开始查询-------------------------- SELECT 省 = PARSENAME(REPLACE(省份,'/','.'),2), ----替换一下 '.' 因为 parsename 只认 '.' 市 = PARSENAME(REPLACE(省份,'/','.'),1)
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-11-20 13:53:54
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([省份] varchar(9))
insert [tb]
select '广东/深圳' union all
select '湖南/长沙' union all
select '北京/北京'
--------------开始查询--------------------------
SELECT
省 = PARSENAME(REPLACE(省份,'/','.'),2), ----替换一下 '.' 因为 parsename 只认 '.'
市 = PARSENAME(REPLACE(省份,'/','.'),1)
FROM tb
----------------结果----------------------------
/*
(3 行受影响)
省 市
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
广东 深圳
湖南 长沙
北京 北京(3 行受影响)
*/
set @t = '广东/惠州'
select left( @t,CHARINDEX('/',@t)-1 ),SUBSTRING(@t,charindex('/',@t)+1,len(@t))