这是函数中的一部分:select isnull(max(cast(replace(right(ltrim(rtrim((Code))),6),'-','0') as int)),0)
from 表A
表A
id code
1 zj11-00025
2 zj12-0056
3 wz050089
4 wzc00140该函数的目的是,使后面6位按1递增,
如:000001 000002 000003
但表A已有原Code,所以在取最大值时,不是很好处理
"wzc00140"此种情况不知如何处理?
报错:将 varchar 值 'C00140' 转换为数据类型为 int 的列时发生语法错误
要求:不能对表A已有原Code进行修改
from 表A
表A
id code
1 zj11-00025
2 zj12-0056
3 wz050089
4 wzc00140该函数的目的是,使后面6位按1递增,
如:000001 000002 000003
但表A已有原Code,所以在取最大值时,不是很好处理
"wzc00140"此种情况不知如何处理?
报错:将 varchar 值 'C00140' 转换为数据类型为 int 的列时发生语法错误
要求:不能对表A已有原Code进行修改
create table a(id int identity(1,1),code varchar(50))
insert into a select 'zj11-00025'
union select 'zj12-0056'
union select 'wz050089'
union select 'wzc00140'
select isnull(max(cast(replace(right(ltrim(rtrim((Code))),6),'-','0') as int)),0)
from A where isnumeric(substring(code,len(Code)-5,6))=1
drop table a
If object_id('ta') is not null
Drop table ta
Go
Create table ta(id int,code varchar(10))
Go
Insert into ta
select 1,'zj11-00025' union all
select 2,'zj12-0056' union all
select 3,'wz050089' union all
select 4,'wzc00140'
Go
--Start
create function f_pos(@s varchar(1000))
returns int
as
begin
declare @d varchar(1000),@i int
set @d = REVERSE(@s)
set @i = 1
while isnumeric(left(@d,@i))=1
set @i = @I + 1
return @I - 1
end
go
Select id,code,right(code,dbo.f_pos(code)) as n from ta
drop function f_pos
--Result:
/*id code n
----------- ---------- ----------
1 zj11-00025 00025
2 zj12-0056 0056
3 wz050089 050089
4 wzc00140 00140(所影响的行数为 4 行)
*/
--End
If object_id('ta') is not null
Drop table ta
Go
Create table ta(id int,code varchar(10))
Go
Insert into ta
select 1,'zj11-00025' union all
select 2,'zj12-0056' union all
select 3,'wz050089' union all
select 4,'wzc00140'
Go
--Start
create function f_pos(@s varchar(1000))
returns int
as
begin
declare @d varchar(1000),@i int
set @d = REVERSE(@s)
set @i = 1
while isnumeric(left(@d,@i))=1
set @i = @I + 1
return @I - 1
end
go
Select id,code,right(code,dbo.f_pos(code)) as n,dbo.f_pos(code) pos from ta
drop function f_pos
--Result:
/*id code n pos
----------- ---------- ---------- -----------
1 zj11-00025 00025 5
2 zj12-0056 0056 4
3 wz050089 050089 6
4 wzc00140 00140 5(所影响的行数为 4 行)
*/
--End
set nocount on;
if not object_id('Tempdb..#A') is null
drop table #A
Go
set nocount on;
Create table #A([id] int,[code] nvarchar(10))
Insert #A
select 1,N'zj11-00025' union all
select 2,N'zj12-0056' union all
select 3,N'wz050089' union all
select 4,N'wzc00140'
Go
Select
max(right([code],patindex('%[^0-9]%',reverse([code]))-1))
from
#A
----------
050089
---
英雄让我葱白一下
select isnull(max(cast(replace(right(ltrim(rtrim((Code))),6),'-','0') as int)),0)
from 表A
where isnumeric(substring(code,len(Code)-5,6))=1
returns int
as
begin
declare @d varchar(1000),@i int
set @d = REVERSE(@s)
set @i = 1
while isnumeric(left(@d,@i))=1 --这儿可以按风的方法用patindex判断
set @i = @I + 1
set @i = @I - 1
return (case when @I> 6 then 6 else @i end)
end
go
如:
表A
id code
1 wz0100001
2 wzc00140where isnumeric(substring(code,len(Code)-5,6))=1 是不是会过滤掉啊?
表A
id code
1 wz01000001
2 wzc00140
If object_id('ta') is not null
Drop table ta
Go
Create table ta(id int,code varchar(10))
Go
Insert into ta
select 1,'wz01000001' union all
select 2,'wzc00140'
Go
--Start
create function f_pos(@s varchar(1000))
returns int
as
begin
declare @d varchar(1000),@i int
set @d = REVERSE(@s)
set @i = 1
while isnumeric(left(@d,@i))=1
set @i = @I + 1
set @i = @I - 1
return (case when @I> 6 then 6 else @i end)
end
go
Select max(cast(right(code,dbo.f_pos(code)) as int)) as maxv from ta
drop function f_pos
--Result:
/*maxv
-----------
140(所影响的行数为 1 行)*/
--End
or isnumeric(substring(code,len(Code)-4,5))=1
select
case when isnumeric(substring(code,len(Code)-5,6))=1
then isnull(max(cast(replace(right(ltrim(rtrim((Code))),6),'-','0') as int)),0)
when isnumeric(substring(code,len(Code)-4,5))=1
then isnull(max(cast(replace(right(ltrim(rtrim((Code))),5),'-','0') as int)),0)
end as maxid
from A
where isnumeric(substring(code,len(Code)-5,6))=1
or isnumeric(substring(code,len(Code)-4,5))=1
select
max(
case when isnumeric(substring(code,len(Code)-5,6))=1
then isnull((cast(replace(right(ltrim(rtrim((Code))),6),'-','0') as int)),0)
when isnumeric(substring(code,len(Code)-4,5))=1
then isnull((cast(replace(right(ltrim(rtrim((Code))),5),'-','0') as int)),0)
end) as maxid
from A
where isnumeric(substring(code,len(Code)-5,6))=1
or isnumeric(substring(code,len(Code)-4,5))=1
from ta
where right(code,dbo.f_pos(code)) = (select max(right(code,dbo.f_pos(code))) from ta)drop function f_pos
--Result:
/*
id code
----------- ----------
2 wzc00140(所影响的行数为 1 行)
*/
--End
set nocount on;
if not object_id('Tempdb..#A') is null
drop table #A
Go
set nocount on;
Create table #A([id] int,[code] nvarchar(10))
Insert #A
select 1,N'zj11-00025' union all
select 2,N'zj12-0056' union all
select 3,N'wz050089' union all
select 4,N'wzc00140'
Go
--> --> (Roy)生成測試數據
set nocount on;
if not object_id('Tempdb..#A') is null
drop table #A
Go
set nocount on;
Create table #A([id] int,[code] nvarchar(10))
Insert #A
select 1,N'zj11-00025' union all
select 2,N'zj12-0056' union all
select 3,N'wz050089' union all
select 4,N'wzc00140'
Go
Select
max(right([code],patindex('%[^0-9]%',reverse([code]))-1))
from
#A
#A我以后会生成这样的代码:0102000236
表A
id code
1 zj11-00025
2 zj12-0056
3 wz050089
4 wzc00140
5 0102000236
6 0103000237后6位递增Select
max(right([code],patindex('%[^0-9]%',reverse([code]))-1))
from
#A是不是会出错啊?
Go
Insert into ta
select 1,'wz01000001' union all
select 2,'wzc00140'
Go
--Start
create function f_pos(@s varchar(1000))
returns int
as
begin
declare @d varchar(1000),@i int
set @d = REVERSE(@s)
set @i = 1
while isnumeric(left(@d,@i))=1
set @i = @I + 1
set @i = @I - 1
return (case when @I> 6 then 6 else @i end)
end
go我以后会生成这样的代码:0102000236
表A
id code
1 zj11-00025
2 zj12-0056
3 wz050089
4 wzc00140 --这是以前不规范的代码
5 0102000236 --这是以后规范的代码,
6 0103000237
这个函数function f_pos会死循环,如何处理?