用于查找仅包含特殊字符的行的 SQL 查询 [英] SQL query for finding rows with special characters only

问题描述

我正在使用 SQL Server 2005.

I am working with SQL Server 2005.

我只需要找出正文"列中有特殊字符的那些行.在以下场景中，结果应该只是 TemplateID = 2 的行.我们如何为此编写查询?

I need to find out only those rows for which there is a special character in "Body" column. In the following scenario, the result should be only the row with TemplateID = 2. How do we write the query for this?

CREATE TABLE #Template (TemplateID INT, Body VARCHAR(100))

INSERT INTO #Template (TemplateID,Body) VALUES (1,'abcd  1234')

INSERT INTO #Template (TemplateID,Body) VALUES (2,'#^!@')

除以下之外的任何内容都是此场景的特殊字符

Anything other than the following is a special character for this scenario

1) Alphabtes

2) Digits

3) Space

推荐答案

SELECT
    TemplateID,
    Body
FROM
    #Template
WHERE
    Body LIKE '%[^0-9a-zA-Z ]%'

括号之间的内容表示数字 (0-9)、小写字母 (a-z)、大写字母 (A-Z) 和空格.^"使它成为这些东西中的一个非".请注意，这与 NOT LIKE '%[0-9a-zA-Z ]%'

The stuff between the brackets says numbers (0-9), lowercase alphas (a-z), uppercase alphas (A-Z) and the space. The "^" makes that a "NOT" one of these things. Note that this is different though than NOT LIKE '%[0-9a-zA-Z ]%'

这篇关于用于查找仅包含特殊字符的行的 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！