22,206
社区成员
发帖
与我相关
我的任务
分享
ABCDEFGHOPQRS0001
ABCDEFGHOPQRS0002
..........
ABCDEFGHOPQRS0121
...........
ABCDEFGHOPQRS9391
...
等等,17位的,最前面的13位为阿拉伯数字,最后的四位为数字
-------------------------------------------------
declare @sql varchar(17)
set @sql='ABCDEFGHOPQRS0001'
select cast(right(@sql,4) as int)
另外发现楼主这句话搞笑。“17位的,最前面的13位为阿拉伯数字”,楼主,你的意思是"ABCDE..."这个是阿拉伯数字??
估计我们伟大的阿拉伯人民听到这句话会疯的。呵呵
select convert(int,right(columnname,4)) from tablename
create function dbo.regexReplace
(
@source varchar(5000), --原字符串
@regexp varchar(1000), --正则表达式
@replace varchar(1000), --替换值
@globalReplace bit = 0, --是否是全局替换
@ignoreCase bit = 0 --是否忽略大小写
)
returnS varchar(1000) AS
begin
declare @hr integer
declare @objRegExp integer
declare @result varchar(5000)
exec @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OASetProperty @objRegExp, 'Global', @globalReplace
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignoreCase
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OAMethod @objRegExp, 'Replace', @result OUTPUT, @source, @replace
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OADestroy @objRegExp
IF @hr <> 0 begin
return null
end
return @result
end
GO
CREATE TABLE T
(
COL VARCHAR(50)
)
INSERT INTO T
SELECT 'ABCDEFGHOPQRS0001' UNION ALL
SELECT 'ABCDEFGHOPQRS0002' UNION ALL
SELECT 'ABCDEFGHOPQRS0121' UNION ALL
SELECT 'ABCDEFGHOPQRS9391'
SELECT RE = CAST(DBO.regexReplace(COL,'[^0-9]','',1,1) AS INT)
FROM t
DROP TABLE T
DROP FUNCTION regexReplace
RE
-----------
1
2
121
9391
(所影响的行数为 4 行)
select cast(right(字段名,4) as int) as x
from tablename
SELECT CAST(RIGHT(ColumnName,4) AS INT) FROM TableName