22,300
社区成员




Select CHAR(ISNULL(MAX(ASCII(EXCEL_COL)),64)+1) MaxExcelCol From SV_EXCEL_FRMT_ID where Frmt_id='AA'
.这样的话超过Z了就不再是所要求的AA,AB.select top 1 Excel_col=case when right(excel_col,1)='z' then replicate('A',len(excel_col)+1)
else left(excel_col,len(excel_col)-1) end +char(ascii(right(excel_col,1))+1)
from excel_frmt_id ORDER BY LEN(EXCEL_COL) DESC,EXCEL_COL DESC
DECLARE @STR VARCHAR(8)
SET @STR='AAAAAAAA'
SET @STR=REVERSE(@STR)
SELECT REVERSE(
STUFF(@STR,
PATINDEX('%[^Z]%', @STR),
1,
CHAR(ASCII(SUBSTRING(@STR,PATINDEX('%[^Z]%', @STR), 1))+1)
)
)
思路:用col来代替你要的列名,
case when right(col,1)='z' then replicate('A',len(列名)+1) else left(col,len(col)-1)end +char(ascii(right(col,1)+1)
也就是说先检测最右边的一个字母,如果为z的话,那么就把A重复现有列的长度次,即生成AAA之类的,如果不到Z,那么截取前N-1个字符,然后加最后一个字符的下一个字符
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] varchar(2))
insert [tb]
select 'A' union all
select 'B' union all
select 'C' union all
select 'D' union all
select 'Z' union all
select 'AA' union all
select 'AB'
---查询---
select
top 1
col
from
tb
order by
len(col) desc,
col desc
---结果---
col
----
AB
(所影响的行数为 1 行)
select
top 1
col
from
tb
order by
len(col) desc
col desc
--這樣?
DECLARE @T TABLE( EXCEL_COL VARCHAR(10))
INSERT @T SELECT 'A'
INSERT @T SELECT 'B'
INSERT @T SELECT 'C'
INSERT @T SELECT 'AB'
INSERT @T SELECT 'D'
INSERT @T SELECT 'AA'
SELECT TOP 1 EXCEL_COL FROM @T ORDER BY LEN(EXCEL_COL) DESC,EXCEL_COL DESC
/*EXCEL_COL
----------
AB
(影響 1 個資料列)*/
Select CHAR(ISNULL(MAX(unicode(EXCEL_COL)),64)+1) MaxExcelCol From SV_EXCEL_FRMT_ID where Frmt_id='AA'