表数据col001
------------------------------
1 Kelly Harrigan
2 Torey Thelin
3 Lindsay McKenna
4 Jen Pittman
5 Annie Goldstein
6 Kyla Burruss
7 Kaki Dudley
8 Lindsay Woodward
9 Margaret Ramsey
10 Kalei Walker
11 Teresa Rosetti
12 Ashley McLellan
现在要将co1001中的数字作为新列(uid),并且消除本列中的数字。
如:
uid coloo1
1 Kelly Harrigan
问题很简单,晚上回来结贴。
------------------------------
1 Kelly Harrigan
2 Torey Thelin
3 Lindsay McKenna
4 Jen Pittman
5 Annie Goldstein
6 Kyla Burruss
7 Kaki Dudley
8 Lindsay Woodward
9 Margaret Ramsey
10 Kalei Walker
11 Teresa Rosetti
12 Ashley McLellan
现在要将co1001中的数字作为新列(uid),并且消除本列中的数字。
如:
uid coloo1
1 Kelly Harrigan
问题很简单,晚上回来结贴。
用charindex
insert @T select '1 Kelly Harrigan'
select
left(col001,charindex(' ',col001)-1),
left(stuff(col001,1,charindex(' ',col001)+2,''),charindex(' ',stuff(col001,1,charindex(' ',col001)+2,''))),
reverse(left(reverse(col001),charindex(' ',reverse(col001))-1))
from
@T
insert into @t
select '1 Kelly Harrigan'
union all select '12 Ashley McLellan'
select id=substring(col,1,charindex(' ',ltrim(col))),col=ltrim(substring(col,charindex(' ',ltrim(col)),len(col)))from @t
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_GetStr]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_GetStr]
GO--分段截取函数
CREATE FUNCTION dbo.f_GetStr(
@s varchar(8000), --包含多个数据项的字符串
@pos int, --要获取的数据项的位置
@split varchar(10) --数据分隔符
)RETURNS varchar(100)
AS
BEGIN
IF @s IS NULL RETURN(NULL)
DECLARE @splitlen int
SELECT @splitlen=LEN(@split+'a')-2
WHILE @pos>1 AND CHARINDEX(@split,@s+@split)>0
SELECT @pos=@pos-1,
@s=STUFF(@s,1,CHARINDEX(@split,@s+@split)+@splitlen,'')
RETURN(ISNULL(LEFT(@s,CHARINDEX(@split,@s+@split)-1),''))
END
GOdeclare @T table(col001 nvarchar(30))
insert @T select '1 Kelly Harrigan'
select
dbo.f_GetStr(col001,1,' '),
dbo.f_GetStr(col001,2,' '),
dbo.f_GetStr(col001,3,' ')
from
@T
(所影响的行数为 1 行)
---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
1 Kelly Harrigan(所影响的行数为 1 行)
declare @t table(col varchar(50))
insert into @t
select '1 Kelly Harrigan'
union all select '12 Ashley McLellan'
--土办法1
select id=substring(col,1,charindex(' ',ltrim(col))),col=ltrim(substring(col,charindex(' ',ltrim(col)),len(col)))from @t
--土办法2
select id=left(col,charindex(' ',ltrim(col))),col=ltrim(right(col,len(col)-charindex(' ',ltrim(col)))) from @t
insert into tb values('1 Kelly Harrigan')
insert into tb values('2 Torey Thelin')
insert into tb values('3 Lindsay McKenna')
insert into tb values('4 Jen Pittman')
insert into tb values('5 Annie Goldstein')
insert into tb values('6 Kyla Burruss')
insert into tb values('7 Kaki Dudley')
insert into tb values('8 Lindsay Woodward')
insert into tb values('9 Margaret Ramsey')
insert into tb values('10 Kalei Walker')
insert into tb values('11 Teresa Rosetti')
insert into tb values('12 Ashley McLellan')
goselect uid = left(col001 , charindex(' ' , col001) - 1) ,
col001 = substring(col001 , charindex(' ' , col001) + 1 , len(col001))
from tbdrop table tb/*
uid col001
------------------------------ ------------------------------
1 Kelly Harrigan
2 Torey Thelin
3 Lindsay McKenna
4 Jen Pittman
5 Annie Goldstein
6 Kyla Burruss
7 Kaki Dudley
8 Lindsay Woodward
9 Margaret Ramsey
10 Kalei Walker
11 Teresa Rosetti
12 Ashley McLellan(所影响的行数为 12 行)
*/
Create function getnumber
(
@fname nvarchar(4000)
)
returns nvarchar(2000)
as
begin
declare @result nvarchar(2000) declare @i int
declare @j int
set @j=1
set @i=len(ltrim(@fname))
set @result=''
while @j<=@i
begin
if ASCii(substring(ltrim(@fname),@j,1))>=48 and ASCii(substring(ltrim(@fname),@j,1))<=57
begin
set @result=@result+cast(substring(ltrim(@fname),@j,1) as nvarchar)
set @j=@j+1
end
else
begin
goto L
end
end
L:
return @result
end
---查询结果
select dbo.getnumber(col001),substring(col001,len(dbo.getnumber(col001))+1,len(col001)-len(dbo.getnumber(col001))) from table1
returns varchar(100)
as
begin
if(@type=1)
begin
set @col001=substring(@col001,1,charindex(' ',@col001)-1)
end
if(@type=2)
begin
set @col001=substring(@col001,charindex(' ',@col001)+1,len(@col001))
end
endselect dbo.www(col001,1),dbo.www(col001,2) from 表
insert into tb values('1 Kelly Harrigan')
insert into tb values('2 Torey Thelin')
insert into tb values('3 Lindsay McKenna')
insert into tb values('4 Jen Pittman')
insert into tb values('5 Annie Goldstein')
insert into tb values('6 Kyla Burruss')
insert into tb values('7 Kaki Dudley')
insert into tb values('8 Lindsay Woodward')
insert into tb values('9 Margaret Ramsey')
insert into tb values('10 Kalei Walker')
insert into tb values('11 Teresa Rosetti')
insert into tb values('12 Ashley McLellan')
goselect uid = left(col001, charindex(' ', col001)-1) ,
col001 = stuff(col001, 1, charindex(' ' , col001), '')
from tbdrop table tb/*
uid col001
------------------------------ -------------------------
1 Kelly Harrigan
2 Torey Thelin
3 Lindsay McKenna
4 Jen Pittman
5 Annie Goldstein
6 Kyla Burruss
7 Kaki Dudley
8 Lindsay Woodward
9 Margaret Ramsey
10 Kalei Walker
11 Teresa Rosetti
12 Ashley McLellan(12 row(s) affected)
*/
insert into tb values('1 Kelly Harrigan')
insert into tb values('2 Torey Thelin')
insert into tb values('3 Lindsay McKenna')
insert into tb values('4 Jen Pittman')
insert into tb values('5 Annie Goldstein')
insert into tb values('6 Kyla Burruss')
insert into tb values('7 Kaki Dudley')
insert into tb values('8 Lindsay Woodward')
insert into tb values('9 Margaret Ramsey')
insert into tb values('10 Kalei Walker')
insert into tb values('11 Teresa Rosetti')
insert into tb values('12 Ashley McLellan')
goselect col001 = PARSENAME(replace(col001 , ' ' , '.'),3) ,
col002 = PARSENAME(replace(col001 , ' ' , '.'),2) ,
col003 = PARSENAME(replace(col001 , ' ' , '.'),1)
from tbdrop table tb/*
col001 col002 col003
------ --------- ---------
1 Kelly Harrigan
2 Torey Thelin
3 Lindsay McKenna
4 Jen Pittman
5 Annie Goldstein
6 Kyla Burruss
7 Kaki Dudley
8 Lindsay Woodward
9 Margaret Ramsey
10 Kalei Walker
11 Teresa Rosetti
12 Ashley McLellan(所影响的行数为 12 行)
*/
http://blog.csdn.net/sxycgxj/archive/2007/06/23/1663790.aspxselect
left(col001,charindex(' ',col001)-1),
substring(col001,charindex(' ',col001),len(col001))
from
Table
/*
功能:实现split功能的函数
*/create function dbo.fn_split
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as begin
declare @i intset @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr)while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1))set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
endif @inputstr <> '\'
insert @temp values(@inputstr)return
end
go--调用declare @s varchar(1000)set @s='1,2,3,4,5,6,7,8,55'select * from dbo.fn_split(@s,',')drop function dbo.fn_split
select substring(col001,1,charindex(' ', col001)) as uid
,substring(col001,charindex(' ', col001),len(col001)) as coloo1
from T_Test1測試結果: uid coloo1
1 Kelly Harrigan
2 Torey Thelin
3 Lindsay McKenna
4 Jen Pittman
5 Annie Goldstein
6 Kyla Burruss
7 Kaki Dudley
8 Lindsay Woodward
9 Margaret Ramsey
10 Kalei Walker
11 Teresa Rosetti
12 Ashley McLellan
declare @col001 varchar(20)
set @col001 = '1 Kelly Harrigan'
select parsename(replace(@col001,' ','.'),3),
parsename(replace(@col001,' ','.'),2)+' '+parsename(replace(@col001,' ','.'),1)
---- --------------
1 Kelly Harrigan
declare @col001 varchar(20)
set @col001 = '123 Kelly Harrigan'
select left(@col001,charindex(' ',@col001)) ,right(@col001,len(@col001)-charindex(' ',@col001))---- --------------
123 Kelly Harrigan
按空格很有局限性。有种投机取巧的感觉,下回要是有的中间有空格,有的中间没空格呢
select
uid=left(c1,charindex(' ',c1)-1),
coloo1 = ltrim(right(c1,len(c1)-charindex(' ',c1)))
from t1