SQL Server中提取字母

[sqlnewleaner]

SQL Server中提取字母

[chuifengde]

提什么字母

[zhou968]

--提取英文
IF OBJECT_ID('DBO.GET_STR') IS NOT NULL
DROP FUNCTION DBO.GET_STR
GO
CREATE FUNCTION DBO.GET_STR(@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^a-z]%',@S) > 0
BEGIN
set @s=stuff(@s,patindex('%[^a-z]%',@s),1,'')
END
RETURN @S
END
GO
--测试
PRINT DBO.GET_STR('sh教科kj的d恒fhh')
GO

[hery2002]

http://topic.csdn.net/u/20080713/00/77925c47-b7fa-4c1b-b307-0328e74a1c09.html?seed=1165773024
--------------------------------------------------------------------

--提取英文
IF OBJECT_ID('DBO.GET_STR') IS NOT NULL
DROP FUNCTION DBO.GET_STR
GO
CREATE FUNCTION DBO.GET_STR(@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^a-z]%',@S) > 0
BEGIN
set @s=stuff(@s,patindex('%[^a-z]%',@s),1,'')
END
RETURN @S
END
GO
--测试
PRINT DBO.GET_STR('呵呵ABC123ABC')
GO

[nzperfect]

http://topic.csdn.net/u/20080713/00/77925c47-b7fa-4c1b-b307-0328e74a1c09.html

[zhou968]

这种帖子很多,楼主先找找再提问啊

[lgxyz]

declare @a varchar(50)
set @a='2a p9 asdf1234ki  9'
while patindex('%[^a-z]%',@a)>0
    begin
        set @a=stuff(@a,patindex('%[^a-z]%',@a),1,'')
    end
select @a
/*                                                 
-------------------------------------------------- 
apasdfki

（所影响的行数为 1 行）
*/

[jasonren]

IF OBJECT_ID('DBO.GET_STR') IS NOT NULL
DROP FUNCTION DBO.GET_STR
GO
CREATE FUNCTION DBO.GET_STR(@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^a-z]%',@S) > 0
BEGIN
set @s=stuff(@s,patindex('%[^a-z]%',@s),1,'')
END
RETURN @S
END
GO
--测试
PRINT DBO.GET_STR('呵呵ABC123ABC')
GO

应该有很多例子.