DECLARE @STR VARCHAR(10) SET @STR='A001-A'SELECT LEFT(@STR,CHARINDEX('-', @STR)-1)
select replace('A001-A','-','')
select Parsename(replace(A列,'-','.'),2) from tb
create table #t(a varchar(20)) insert into #t select 'A0001-1' union select 'B0002-1' union select 'CA0000203-3'select substring(a,1,charindex('-',a)-1) from #t
SELECT LEFT(A,CHARINDEX('-',A)-1) FROM TB
...--失误 select substring(A,1,charindex('-',A)-1) from tb
怎么各位都用substring呢?用replace效率难道不高吗?
-- ========================================= -- -----------t_mac 小编------------- ---希望有天成为大虾---- -- =========================================IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb GO CREATE TABLE tb(a varchar(100)) go insert into tb select 'A001-A' union all select 'BA001-B' union all select 'AS001-AB' go SELECT tb=substring(a,1,CHARINDEX('-',a)-1) from tb /*------------ A001 BA001 AS001 -------*/
declare @T table(col nvarchar(20)) insert @T select 'A001-A' union all select 'A002-B' union all select 'A003-C' union all select 'A004-D'select col=left(col,charindex('-',col)-1) from @Tcol -------------------- A001 A002 A003 A004(4 行受影响)
-- ========================================= -- -----------t_mac 小编------------- ---希望有天成为大虾---- -- =========================================IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb GO CREATE TABLE tb(a varchar(100)) go insert into tb select 'A001-A' union all select 'BA001-B' union all select 'AS001-AB' go SELECT tb=REPLACE(a,right(a,len(a)-CHARINDEX('-',a)+1),'') from tb /*------------ A001 BA001 AS001 -------*/
declare @s varchar(80) set @s='A001-A'select left(@s,charindex('-',@s)-1)
-- ========================================= -- -----------t_mac 小编------------- ---希望有天成为大虾---- -- =========================================IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb GO CREATE TABLE tb(a varchar(100)) go insert into tb select 'A001-A' union all select 'BA001-B' union all select 'AS001-AB' go select col=left(a,charindex('-',a)-1) from tb /*------------ A001 BA001 AS001 -------*/
declare @s varchar(80) set @s='A001-A' select substring(@s,1,charindex('-',@s)-1)
set @s='A001-A'
select left(@s,charindex('-',@s)-1)/*
--------------------------------------------------------------------------------
A001(所影响的行数为 1 行)*/
SET @STR='A001-A'SELECT LEFT(@STR,CHARINDEX('-', @STR)-1)
select replace('A001-A','-','')
insert into #t select 'A0001-1' union select 'B0002-1' union select 'CA0000203-3'select substring(a,1,charindex('-',a)-1) from #t
select substring(A,1,charindex('-',A)-1) from tb
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(a varchar(100))
go
insert into tb
select 'A001-A' union all
select 'BA001-B' union all
select 'AS001-AB'
go
SELECT tb=substring(a,1,CHARINDEX('-',a)-1)
from tb
/*------------
A001
BA001
AS001
-------*/
insert @T
select 'A001-A' union all
select 'A002-B' union all
select 'A003-C' union all
select 'A004-D'select col=left(col,charindex('-',col)-1)
from @Tcol
--------------------
A001
A002
A003
A004(4 行受影响)
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(a varchar(100))
go
insert into tb
select 'A001-A' union all
select 'BA001-B' union all
select 'AS001-AB'
go
SELECT tb=REPLACE(a,right(a,len(a)-CHARINDEX('-',a)+1),'')
from tb
/*------------
A001
BA001
AS001
-------*/
set @s='A001-A'select left(@s,charindex('-',@s)-1)
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(a varchar(100))
go
insert into tb
select 'A001-A' union all
select 'BA001-B' union all
select 'AS001-AB'
go
select col=left(a,charindex('-',a)-1)
from tb
/*------------
A001
BA001
AS001
-------*/
set @s='A001-A'
select substring(@s,1,charindex('-',@s)-1)