--提取数字 IF OBJECT_ID('DBO.GET_NUMBER2') IS NOT NULL DROP FUNCTION DBO.GET_NUMBER2 GO CREATE FUNCTION DBO.GET_NUMBER2(@S VARCHAR(100)) RETURNS VARCHAR(100) AS BEGIN WHILE PATINDEX('%[^0-9]%',@S) > 0 BEGIN set @s=stuff(@s,patindex('%[^0-9]%',@s),1,'') END RETURN @S END GO --测试 PRINT DBO.GET_NUMBER('呵呵ABC123ABC') GO --123 -------------------------------------------------------------------- --提取英文 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 -------------------------------------------------------------------- --提取中文 IF OBJECT_ID('DBO.CHINA_STR') IS NOT NULL DROP FUNCTION DBO.CHINA_STR GO CREATE FUNCTION DBO.CHINA_STR(@S NVARCHAR(100)) RETURNS VARCHAR(100) AS BEGIN WHILE PATINDEX('%[^吖-座]%',@S) > 0 SET @S = STUFF(@S,PATINDEX('%[^吖-座]%',@S),1,N'') RETURN @S END GO PRINT DBO.CHINA_STR('呵呵ABC123ABC') GOSELECT * FROM (select 'ASDKG論壇K联通DL' as col)TB WHERE COL LIKE N'%[吖-咗]%'
select * from [Table] where patindex('%[^0-9a-z]%',Col)=0
select * from tb where patindex('%[^0-9a-zA-Z]%',col)=0
--提取数字 IF OBJECT_ID('DBO.GET_NUMBER2') IS NOT NULL DROP FUNCTION DBO.GET_NUMBER2 GO CREATE FUNCTION DBO.GET_NUMBER2(@S VARCHAR(100)) RETURNS VARCHAR(100) AS BEGIN WHILE PATINDEX('%[^0-9]%',@S) > 0 BEGIN set @s=stuff(@s,patindex('%[^0-9]%',@s),1,'') END RETURN @S END GO --测试 PRINT DBO.GET_NUMBER('呵呵ABC123ABC') GO --123 -------------------------------------------------------------------- --提取英文 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 -------------------------------------------------------------------- --提取中文 IF OBJECT_ID('DBO.CHINA_STR') IS NOT NULL DROP FUNCTION DBO.CHINA_STR GO CREATE FUNCTION DBO.CHINA_STR(@S NVARCHAR(100)) RETURNS VARCHAR(100) AS BEGIN WHILE PATINDEX('%[^吖-座]%',@S) > 0 SET @S = STUFF(@S,PATINDEX('%[^吖-座]%',@S),1,N'') RETURN @S END GO PRINT DBO.CHINA_STR('呵呵ABC123ABC') GOSELECT * FROM (select 'ASDKG論壇K联通DL' as col)TB WHERE COL LIKE N'%[吖-咗]%'
patindex('%[^0-9a-zA-Z]%',字段)>0
where not patindex('%[^0-9a-zA-Z]%',字段)>0
ALTER FUNCTION dbo.F_Get_STR (@S VARCHAR(100)) RETURNS VARCHAR(100) ASBEGIN DECLARE @v_len_str1 VARCHAR(100); DECLARE @v_len_str2 VARCHAR(100); DECLARE @return VARCHAR(100); SET @v_len_str1 = len(@S); WHILE PATINDEX('%[^a-z]%',@S)>0 BEGIN set @s=stuff(@s,patindex('%[^a-z0-9]%',@s),1,'') END SET @v_len_str2 = len(@S); IF(@v_len_str1=@v_len_str2) SET @return = '只含字母、数字'; ELSE SET @return ='除含字母、数字外,还有其他字符'; RETURN @return; END GOselect dbo.F_Get_STR('luoyoumou%');
-- http://www.cnblogs.com/SAL2928/archive/2009/12/28/1633761.html-- sql获取字母:CREATE FUNCTION dbo.F_Get_STR (@S VARCHAR(100)) RETURNS VARCHAR(100) AS BEGIN WHILE PATINDEX('%[^a-z]%',@S)>0 BEGIN set @s=stuff(@s,patindex('%[^a-z0-9]%',@s),1,'') END RETURN @S END GO-- sql获取数字: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-- sql获取非数字:create function dbo.F_Get_NANnumber (@S varchar(100)) returns varchar(500) AS begin while PATINDEX('%[0-9]%',@S)>0 begin set @s=stuff(@s,patindex('%[0-9]%',@s),1,'') end return @s end GO
IF @string NOT LIKE '%[^0-9A-Za-z]%' COLLATE Chinese_PRC_BIN SELECT '只有英文字符和数字'
IF OBJECT_ID('DBO.GET_NUMBER2') IS NOT NULL
DROP FUNCTION DBO.GET_NUMBER2
GO
CREATE FUNCTION DBO.GET_NUMBER2(@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^0-9]%',@S) > 0
BEGIN
set @s=stuff(@s,patindex('%[^0-9]%',@s),1,'')
END
RETURN @S
END
GO
--测试
PRINT DBO.GET_NUMBER('呵呵ABC123ABC')
GO
--123
--------------------------------------------------------------------
--提取英文
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
--------------------------------------------------------------------
--提取中文
IF OBJECT_ID('DBO.CHINA_STR') IS NOT NULL
DROP FUNCTION DBO.CHINA_STR
GO
CREATE FUNCTION DBO.CHINA_STR(@S NVARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^吖-座]%',@S) > 0
SET @S = STUFF(@S,PATINDEX('%[^吖-座]%',@S),1,N'')
RETURN @S
END
GO
PRINT DBO.CHINA_STR('呵呵ABC123ABC')
GOSELECT * FROM (select 'ASDKG論壇K联通DL' as col)TB WHERE COL LIKE N'%[吖-咗]%'
where patindex('%[^0-9a-z]%',Col)=0
IF OBJECT_ID('DBO.GET_NUMBER2') IS NOT NULL
DROP FUNCTION DBO.GET_NUMBER2
GO
CREATE FUNCTION DBO.GET_NUMBER2(@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^0-9]%',@S) > 0
BEGIN
set @s=stuff(@s,patindex('%[^0-9]%',@s),1,'')
END
RETURN @S
END
GO
--测试
PRINT DBO.GET_NUMBER('呵呵ABC123ABC')
GO
--123
--------------------------------------------------------------------
--提取英文
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
--------------------------------------------------------------------
--提取中文
IF OBJECT_ID('DBO.CHINA_STR') IS NOT NULL
DROP FUNCTION DBO.CHINA_STR
GO
CREATE FUNCTION DBO.CHINA_STR(@S NVARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^吖-座]%',@S) > 0
SET @S = STUFF(@S,PATINDEX('%[^吖-座]%',@S),1,N'')
RETURN @S
END
GO
PRINT DBO.CHINA_STR('呵呵ABC123ABC')
GOSELECT * FROM (select 'ASDKG論壇K联通DL' as col)TB WHERE COL LIKE N'%[吖-咗]%'
where not patindex('%[^0-9a-zA-Z]%',字段)>0
RETURNS VARCHAR(100)
ASBEGIN
DECLARE @v_len_str1 VARCHAR(100);
DECLARE @v_len_str2 VARCHAR(100);
DECLARE @return VARCHAR(100); SET @v_len_str1 = len(@S);
WHILE PATINDEX('%[^a-z]%',@S)>0
BEGIN
set @s=stuff(@s,patindex('%[^a-z0-9]%',@s),1,'')
END SET @v_len_str2 = len(@S);
IF(@v_len_str1=@v_len_str2)
SET @return = '只含字母、数字';
ELSE
SET @return ='除含字母、数字外,还有其他字符';
RETURN @return;
END
GOselect dbo.F_Get_STR('luoyoumou%');
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^a-z]%',@S)>0
BEGIN
set @s=stuff(@s,patindex('%[^a-z0-9]%',@s),1,'')
END
RETURN @S
END
GO-- sql获取数字: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-- sql获取非数字:create function dbo.F_Get_NANnumber (@S varchar(100))
returns varchar(500)
AS
begin
while PATINDEX('%[0-9]%',@S)>0
begin
set @s=stuff(@s,patindex('%[0-9]%',@s),1,'')
end
return @s
end
GO
IF @string NOT LIKE '%[^0-9A-Za-z]%' COLLATE Chinese_PRC_BIN
SELECT '只有英文字符和数字'