并把数据分割,只取第二个‘-’以后的数据 ,分别插入对应列中求方法!!!!!!!!!!!!!
表名 tb 列名 ED 下面是数据
ED uc
asd-ew-we-tr
gf-ewr-we-fh
gd-fe-we
gr-gr-gr
想查找ED下的数据, 并把数据分割,只取第二个‘-’以后的数据 ,分别插入uc列中 求方法!!!!!!!!!!!!
[最优解释]
update TB set uc=SUBSTRING(ED,charindex('-',ED)+1,len(ED)-charindex('-',ED)+1)
[其他解释]
--DECLARE ed VARCHAR(128)
--SET ed='asd-ew-we-tr'
--如果uc是别的表的列:
INSERT INTO xx (uc)
SELECT SUBSTRING(ed,CHARINDEX('-',ed,PATINDEX('%-%',ed))+1,LEN(ed)-CHARINDEX('-',ed,PATINDEX('%-%',ed)))
FROM ed
--如果uc在用一个表:
UPDATE TB
SET uc=SUBSTRING(ed,CHARINDEX('-',ed,PATINDEX('%-%',ed))+1,LEN(ed)-CHARINDEX('-',ed,PATINDEX('%-%',ed)))
--以下是测试数据
use tempdb
go
if not OBJECT_ID('tempdb..#tb') IS NULL
drop table #tb
CREATE TABLE #tb(ED varchar(50),uc varchar(50) null)
insert into #tb(ED)
select 'asd-ew-we-tr' union all
select 'gf-ewr-we-fh' union all
select 'gd-fe-we' union all
select 'gr-gr-gr'
--以下是插入语句 -将#tb换掉即可
update #tb
set
uc=SUBSTRING(ED,CHARINDEX('-',ED,CHARINDEX('-',ED)+1)+1,lEN(ED)-CHARINDEX('-',ED,CHARINDEX('-',ED)+1)+1)
--以下是查看效果
select * from #tb
/*
EDuc
asd-ew-we-trwe-tr
gf-ewr-we-fhwe-fh
gd-fe-wewe
gr-gr-grgr
*/
----------------------------
-- Author :TravyLee(物是人非事事休,欲语泪先流!)
-- Date :2012-11-20 09:07:19
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
--Jul 9 2008 14:43:34
--Copyright (c) 1988-2008 Microsoft Corporation
--Developer Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------
--> 测试数据:[test]
if object_id('[test]') is not null drop table [test]
go
create table [test]([ED] varchar(12),[uc] varchar(10))
insert [test]
select 'asd-ew-we-tr',null union all
select 'gf-ewr-we-fh',null union all
select 'gd-fe-we',null union all
select 'gr-gr-gr',null
go
update test
set [uc]=right([ED],len([ED])-charindex('-',[ED],charindex('-',[ED])+1))
/*
EDuc
asd-ew-we-trwe-tr
gf-ewr-we-fhwe-fh
gd-fe-wewe
gr-gr-grgr
*/
--> 测试数据:[ED]
if object_id('[ED]') is not null drop table [ED]
GO
create table [ED]([col] varchar(12),uc VARCHAR(20))
insert [ED](col)
select 'asd-ew-we-tr' union all
select 'gf-ewr-we-fh' union all
select 'gd-fe-we' union all
select 'gr-gr-gr'
UPDATE ED
SET
uc = PARSENAME(STUFF(col,CHARINDEX('-',col,CHARINDEX('-',[col])+1),1,'.'),1)
SELECT * FROM ED e
/*
col uc
------------ --------------------
asd-ew-we-tr we-tr
gf-ewr-we-fh we-fh
gd-fe-we we
gr-gr-gr gr
(4 行受影响)
*/
drop table [ED]
CREATE TABLE tb(ed varchar(40),uc varchar(40))
INSERT INTO tb SELECT 'asd-ew-we-tr','' UNION all
SELECT 'gf-ewr-we-fh','' UNION all
SELECT 'gd-fe-we','' UNION all
SELECT 'gr-gr-gr',''
UPDATE tb SET uc=
RIGHT(ed,(len(ed)-charindex('-',ed))-charindex('-',RIGHT(ed,len(ed)-charindex('-',ed)))) FROM tb
SELECT * FROM tb