select left([Columns],charindex(' ',[Columns]) - 1) as qianK, right([Columns],len([Columns]) - charindex(' ',[Columns])) as houK from tb
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2011-05-09 16:16:08 -- Verstion: -- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation -- Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([Columns] varchar(60)) insert [tb] select '张三 财务' union all select '王小明 主管' union all select '李四 职员' --------------开始查询-------------------------- select left([Columns],charindex(' ',[Columns]) - 1) as name, right([Columns],len([Columns]) - charindex(' ',[Columns])) as info from tb----------------结果---------------------------- /* name info ------------------------------------------------------------ ------------------------------------------------------------ 张三 财务 王小明 主管 李四 职员(3 行受影响)*/
declare @table table (Columns varchar(15)) insert into @table select '张三 财务' union all select '王小明 主管' union all select '李四 职员'select parsename(replace(Columns,' ','.'),2) as Name , parsename(replace(Columns,' ','.'),1) as Info from @table
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2011-05-09 16:16:08 -- Verstion: -- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation -- Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([Columns] varchar(60)) insert [tb] select '张三 财务' union all select '王小明 主管' union all select '李四 职员' --------------开始查询-------------------------- select PARSENAME(REPLACE([columns],' ','.'),2) as name, PARSENAME(REPLACE([columns],' ','.'),1) as info from tb----------------结果---------------------------- /* name info ------------------------------------------------------------ ------------------------------------------------------------ 张三 财务 王小明 主管 李四 职员(3 行受影响)*/
select left([Columns],charindex(' ',[Columns]) - 1) as qianK,
right([Columns],len([Columns]) - charindex(' ',[Columns])) as houK
from tb
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-05-09 16:16:08
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Columns] varchar(60))
insert [tb]
select '张三 财务' union all
select '王小明 主管' union all
select '李四 职员'
--------------开始查询--------------------------
select
left([Columns],charindex(' ',[Columns]) - 1) as name,
right([Columns],len([Columns]) - charindex(' ',[Columns])) as info
from
tb----------------结果----------------------------
/* name info
------------------------------------------------------------ ------------------------------------------------------------
张三 财务
王小明 主管
李四 职员(3 行受影响)*/
declare @table table (Columns varchar(15))
insert into @table
select '张三 财务' union all
select '王小明 主管' union all
select '李四 职员'select parsename(replace(Columns,' ','.'),2) as Name ,
parsename(replace(Columns,' ','.'),1) as Info from @table
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-05-09 16:16:08
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Columns] varchar(60))
insert [tb]
select '张三 财务' union all
select '王小明 主管' union all
select '李四 职员'
--------------开始查询--------------------------
select
PARSENAME(REPLACE([columns],' ','.'),2) as name,
PARSENAME(REPLACE([columns],' ','.'),1) as info
from
tb----------------结果----------------------------
/* name info
------------------------------------------------------------ ------------------------------------------------------------
张三 财务
王小明 主管
李四 职员(3 行受影响)*/