怎样判断字符串只有 英文字符和数字?

[验证码识别]

判断字符串只有 英文字符和数字

SQL要怎么写,越具体越好

[永生天地]

--提取数字
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')
GO



SELECT * FROM (select 'ASDKG論壇K联通DL' as col)TB WHERE COL LIKE N'%[吖-咗]%' 

[chuifengde]

select * from [Table] 
where patindex('%[^0-9a-z]%',Col)=0

[htl258_Tony]

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')
GO

SELECT * FROM (select 'ASDKG論壇K联通DL' as col)TB WHERE COL LIKE N'%[吖-咗]%' 

[GOODlivelife]

patindex('%[^0-9a-zA-Z]%',字段)>0

[GOODlivelife]

[Quote=引用 5 楼 goodlivelife 的回复:]
SQL code
patindex('%[^0-9a-zA-Z]%',字段)>0
[/Quote]

where not patindex('%[^0-9a-zA-Z]%',字段)>0

[luoyoumou]

ALTER FUNCTION dbo.F_Get_STR (@S VARCHAR(100))
RETURNS VARCHAR(100)
AS

BEGIN
  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
GO

select dbo.F_Get_STR('luoyoumou%');

[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

[rmljoe]

学习了。。。

[feilniu]

IF @string NOT LIKE '%[^0-9A-Za-z]%' COLLATE Chinese_PRC_BIN
	SELECT '只有英文字符和数字'

[feilniu]

不考虑排序规则的字符串比较，结果正确都是蒙得巧。