27,579
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2012-05-25 16:53:19
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([caseno] varchar(21))
insert [tb]
select '1076D67D71_0_10516-13' union all
select '16D7D131_0_10516-13'
--------------开始查询--------------------------
select * into #t from(select parsename(replace(caseno,'_','.'),3) as a from tb)t
--select * from #t
select left(a,len(a)-1)+ltrim(cast(right(a,1) as int)-1) from #t
drop table #t
----------------结果----------------------------
/* --------------------------------------------------------------------------------------------------------------------------------------------
1076D67D70
16D7D130
(2 行受影响)
*/
--建个function
Create Function dbo.usf_test(@s varchar(100))
returns varchar(100)
as
begin
declare @tmp1 varchar(100),@tmp2 varchar(100), @num int,@re varchar(100)
select @tmp1=left(@s,case when charindex('_',@s)-1 > 0 then charindex('_',@s)-1 else len(@s) end)
,@tmp2= left(reverse(@tmp1), case when patindex('%[^0-9]%',reverse(@tmp1))-1>=0 then patindex('%[^0-9]%',reverse(@tmp1))-1 else len(@tmp1) end)
if isnumeric(right(@tmp2,1))=1
begin
if replace(@tmp2,'0','')<>''
select @num=convert(int,reverse(@tmp2))-1
,@re= stuff(@tmp1,len(@tmp1)-len(@tmp2)+1,len(@tmp2),right(replace(space(len(@tmp2)),' ','0')+rtrim(@num),len(@tmp2)))
else
select @re=@tmp1
end
else
select @re=@tmp1
return @re
end
GO
--以后可以直接用。。。
select dbo.usf_test('1078A10D10_0_10516-13')
DECLARE @Str VARCHAR(100)
SET @Str = '76D67D71_0_10516-13'
SELECT LEFT(@Str,CHARINDEX('_',@Str) - 2) + CAST(CAST(SUBSTRING(@Str,CHARINDEX('_',@Str) - 1,1) AS INT) - 1 AS VARCHAR(10))
SELECT SUBSTRING(caseno,1,9)+LTRIM((CAST(SUBSTRING(caseno,10,1) AS INT)-1))