34,590
社区成员
发帖
与我相关
我的任务
分享
DECLARE @a TABLE(a VARCHAR(200))
INSERT @a SELECT '13asd123'
UNION ALL SELECT '12a123ddf33'
UNION ALL SELECT 'addf123'
UNION ALL SELECT 'as5d'
UNION ALL SELECT 'qwe'
SELECT CASE WHEN PATINDEX('%[0-9]%',a)=1 THEN LEFT(a,PATINDEX('%[^0-9]%',a)-1)
WHEN PATINDEX('%[0-9]%',reverse(a))=1 THEN reverse(left(reverse(a),PATINDEX('%[^0-9]%',reverse(a))-1))
ELSE NULL
END a
FROM @a
--
a
13
12
123
NULL
NULL
DECLARE @a TABLE(a VARCHAR(200))
INSERT @a SELECT 'asd123'
UNION ALL SELECT '123qwe'
UNION ALL SELECT 'asd'
UNION ALL SELECT 'qwe'
SELECT CASE WHEN PATINDEX('%[0-9]%',a)=1 THEN LEFT(a,PATINDEX('%[^0-9]%',a)-1)
WHEN PATINDEX('%[0-9]%',a)>1 THEN STUFF(a,1,PATINDEX('%[0-9]%',a)-1,'')
ELSE NULL
END a
FROM @a
create function dbo.getnumer(@str nvarchar(100))
returns nvarchar(100)
as
begin
declare @bz int,@ret nvarchar(100)
set @ret=''
if (@str like '[0-9]%')
set @bz=1
else if (@str like '%[0-9]')
begin
set @bz=2
set @str=reverse(@str)
end else
set @bz=0
if (@bz>0)
begin
while @str like '[0-9]%'
begin
set @ret=@ret+left(@str,1)
set @str=stuff(@str,1,1,'')
end
if (@bz=2)
set @ret=reverse(@ret)
end
return @ret
end
IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([col] NVARCHAR(10))
INSERT [tb]
SELECT 'asd123' UNION ALL
SELECT '123qwe' UNION ALL
SELECT 'asd' UNION ALL
SELECT 'qwe' UNION ALL
SELECT 'qw55e12' UNION ALL
SELECT '111qwe122'
GO
--SELECT * FROM [tb]
-->SQL查询如下:
IF NOT object_id('FN_STR') IS NULL
DROP FUNCTION fn_str
GO
CREATE FUNCTION fn_str(@col NVARCHAR(50))
RETURNS INT
BEGIN
SET @col=RTRIM(LTRIM(@col))
IF PATINDEX('%[0-9]%',LEFT(@col,1))+PATINDEX('%[0-9]%',RIGHT(@col,1))=0
RETURN NULL
ELSE
BEGIN
IF PATINDEX('%[0-9]%',LEFT(@col,1))>0
BEGIN
SET @col=STUFF(@col,1,PATINDEX('%[0-9]%',@col)-1,'')+'a'
SET @col=LEFT(@col,PATINDEX('%[^0-9]%',@col)-1)
END
ELSE
SET @col=RIGHT(@col,PATINDEX('%[^0-9]%',REVERSE(@col))-1)
END
RETURN @col
END
GO
SELECT dbo.FN_STR(col) as a FROM tb
/*
a
-----------
123
123
NULL
NULL
12
111
(6 行受影响)
*/
上面还有点小问题,再修改一下
create function dbo.F_Get_Number (@S varchar(100))
returns int
AS
begin
while PATINDEX('%[^0-9]%',@S)>0
begin
set @s=stuff(@s,patindex('%[^0-9]%',@s),1,'')
end
return cast(@S as int)
end
GO
--> 生成测试数据表:tb
IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([col] NVARCHAR(10))
INSERT [tb]
SELECT 'asd123' UNION ALL
SELECT '123qwe' UNION ALL
SELECT 'asd' UNION ALL
SELECT 'qwe' UNION ALL
SELECT 'qw55e' UNION ALL
SELECT '111qwe122'
GO
--用函数来做
create function [dbo].[F_Get_Number] (@S varchar(100))
returns int
AS
begin
while PATINDEX('%[^0-9]%',@S)>0
begin
set @s=stuff(@s,patindex('%[^0-9]%',@s),1,'')
end
return cast(@S as int)
end
GO
SELECT dbo.[F_Get_Number]([col]) FROM tb
/*
-----------
123
123
0
0
55
111122
(6 行受影响)
*/
IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([col] NVARCHAR(10))
INSERT [tb]
SELECT 'asd123' UNION ALL
SELECT '123qwe' UNION ALL
SELECT 'asd' UNION ALL
SELECT 'qwe' UNION ALL
SELECT 'qw55e' UNION ALL
SELECT '111qwe122'
GO
--SELECT * FROM [tb]
-->SQL查询如下:
IF NOT object_id('FN_STR') IS NULL
DROP FUNCTION fn_str
GO
CREATE FUNCTION fn_str(@col NVARCHAR(50))
RETURNS INT
BEGIN
SET @col=RTRIM(LTRIM(@col))
IF PATINDEX('%[0-9]%',LEFT(@col,1))+PATINDEX('%[0-9]%',RIGHT(@col,1))=0
RETURN NULL
ELSE
BEGIN
SET @col=STUFF(@col,1,PATINDEX('%[0-9]%',@col)-1,'')+'a'
SET @col=LEFT(@col,PATINDEX('%[^0-9]%',@col)-1)
END
RETURN @col
END
GO
SELECT dbo.FN_STR(col) as a FROM tb
/*
a
-----------
123
123
NULL
NULL
NULL
111
(6 行受影响)
*/
修正。DECLARE @a TABLE(a VARCHAR(200))
INSERT @a SELECT 'asd123'
UNION ALL SELECT '123qwe'
UNION ALL SELECT 'asd'
UNION ALL SELECT 'qwe'
SELECT CASE WHEN PATINDEX('%[0-9]%',a)=1 THEN LEFT(a,PATINDEX('%[^0-9]%',a)-1)
WHEN PATINDEX('%[0-9]%',a)>1 THEN STUFF(a,1,PATINDEX('%[0-9]%',a)-1,'')
ELSE NULL
END a
FROM @a
--result
/*a
------------------------------
123
123
NULL
NULL
(所影响的行数为 4 行)
*/
/*---------------------------------
-- Author : htl258(Tony)
-- Date : 2009-09-14 15:57:06
-- Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
Mar 29 2009 10:27:29
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
---------------------------------*/
--> 生成测试数据表:tb
IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([col] NVARCHAR(10))
INSERT [tb]
SELECT 'asd123' UNION ALL
SELECT '123qwe' UNION ALL
SELECT 'asd' UNION ALL
SELECT 'qwe'
GO
--SELECT * FROM [tb]
-->SQL查询如下:
IF NOT object_id('FN_STR') IS NULL
DROP FUNCTION fn_str
GO
CREATE FUNCTION fn_str(@col NVARCHAR(50))
RETURNS INT
BEGIN
IF PATINDEX('%[0-9]%',@col)=0
RETURN NULL
ELSE
BEGIN
SET @col=STUFF(@col,1,PATINDEX('%[0-9]%',@col)-1,'')+'a'
SET @col=LEFT(@col,PATINDEX('%[^0-9]%',@col)-1)
END
RETURN @col
END
GO
SELECT dbo.FN_STR(col) as a FROM tb
/*
a
-----------
123
123
NULL
NULL
(4 行受影响)
*/