一个关于匹配的问题。以前没接触过，不知如何下手，请朋友们帮忙！

[gaolch]

表1有个字段name，varchar（20）.
现在想找出name只包含字母，数字和'-'的记录。其他的记录从表中删掉它们！
请问怎么写？谢谢！

只要解决加多30分结贴！小弟分不多，用光了，请见谅，但很诚信！请大家多多帮忙！

[chuifengde]

DECLARE @A TABLE(ID1 varchar(10),Name1 varchar(100),ID varchar(10))
insert @a select '1001', 'aaFJKLAA23423-SDJLAKSD', '001'
union all select '1002', 'bb23SDF', '001'
union all select '1003', '你好', '002'
union all select '1004', 'dd', '002'

select * from @a where patindex('%[a-z]%',NAME1)>0 or patindex('%-%',NAME1)>0

[chuifengde]

中间是or是and 你看着改，我也不完全懂你的意思

[chuifengde]

select * from @a where patindex('%[a-z]%',NAME1)>0 or patindex('%-%',NAME1)>0 and patindex('%[0-9]%',NAME1)>0

[csdnweii]

ascii( character_expression ) :返回字符表达式最左端字符的 ASCII 代码值。

你写个自定义函数:
create function f_ascii (@str varchar(20)) returns int as
begin
while len(@str)>0
begin
if ascii(@str) not in (那些字符的ascii码值)
return 1 ---包含其它字符
end
return 0 ---不包含其它字符
end


删除含有其它字符的纪录：
delete TableName where dbo.f_ascii([name])=1

[yczealot]

DROP TABLE A
DROP FUNCTION ISVALID

CREATE TABLE A(ID1 VARCHAR(10),NAME1 VARCHAR(100),ID VARCHAR(10))
INSERT A SELECT '1001', '[e-mail]', '001'
UNION ALL SELECT '1002', '123456-8a', '001'
UNION ALL SELECT '1003', '学无涯', '002'
UNION ALL SELECT '1004', 'yc_zealot', '002'
UNION ALL SELECT '1005', 'yc123_vfv', '002'
UNION ALL SELECT '1005', 'yc123-vfv', '002'
GO

CREATE FUNCTION ISVALID
(@NAME VARCHAR(100))
RETURNS INT
AS
BEGIN
DECLARE
@I INT,
@ISVALID INT

SET @I=1
SET @ISVALID=0

WHILE @I<=LEN(@NAME)
BEGIN
IF EXISTS(SELECT 1 WHERE SUBSTRING(@NAME,@I,1) NOT LIKE '[A-Z]' AND SUBSTRING(@NAME,@I,1) NOT LIKE '[0-9]' AND SUBSTRING(@NAME,@I,1) NOT LIKE '[-]')
BEGIN
SET @ISVALID=@ISVALID+1
END
SET @I=@I+1
END

RETURN @ISVALID
--PRINT @NAME1
END

GO

SELECT * FROM A WHERE DBO.ISVALID(NAME1)>0
DELETE * FROM A WHERE DBO.ISVALID(NAME1)>0

[gaolch]

不错，很感谢各位！加分结贴！