如何在带有数字字段的 SQL 中使用 LIKE 条件？

How to use LIKE condition in SQL with numeric field?

提问by

I'm using this query to get some specific data: "select * from emp where emp_name LIKE 's%'";

我正在使用此查询来获取一些特定数据：“select * from emp where emp_name LIKE 's%'”;

emp_nam is character field, how can I use the same logic condition with numeric field? something like:

emp_nam 是字符字段，如何使用与数字字段相同的逻辑条件？就像是：

"select * from emp where emp_id ????

"select * from emp where emp_id ????

where emp_id is numeric field.

其中 emp_id 是数字字段。

Thanks,

谢谢，

回答by Robin Day

You can't do a wildcard on a number, however, if you really need to, you can convert the number to a varchar and then perform the wildcard match.

您不能对数字使用通配符，但是，如果您确实需要，可以将数字转换为 varchar，然后执行通配符匹配。

eg.

例如。

SELECT * FROM emp WHERE CONVERT(varchar(20), emp_id) LIKE '1%'

回答by Jason DeFontes

In Access you can concatenate the numeric field with an empty string to coerce it into a string that you can compare using LIKE:

在 Access 中，您可以将数字字段与空字符串连接起来，将其强制转换为可以使用 LIKE 进行比较的字符串：

select * from emp where emp_id & '' like '123*'

Also note that Access uses * not % as the wildcard character. See: Like Operator (Microsoft Access SQL).

另请注意，Access 使用 * 而不是 % 作为通配符。请参阅：Like Operator (Microsoft Access SQL)。

回答by tuergeist

No, you can't use LIKEfor numeric fields. Try using <,>or =, >=, <=;)

不，您不能LIKE用于数字字段。尝试使用<,>或=, >=, <=;)

If you want to search in a numeric field for things like "beginning with 12" or sth like this, your design not fits your needs.

如果您想在数字字段中搜索诸如“以 12 开头”之类的内容，那么您的设计不符合您的需求。

回答by mukul

'%' wild card worked for me on MS -SQL server. See http://technet.microsoft.com/en-us/library/aa933232%28v=sql.80%29.aspx.

“%”通配符在 MS-SQL 服务器上对我有用。请参阅http://technet.microsoft.com/en-us/library/aa933232%28v=sql.80%29.aspx。

The query select * from MyTable where ID like '548%'works on MS-SQL Server 2008 R2 and returns results with ids 5481,5485 etc. ID column is inttype.

该查询 select * from MyTable where ID like '548%'适用于 MS-SQL Server 2008 R2，并返回 ID 为 5481、5485 等的结果。ID 列是int类型。

回答by onedaywhen

In Access database engine SQL syntax, to use the %wildcard character EITHER you must be using ANSI-92 Query Mode OR use the ALIKEkeyword in place of the LIKEkeyword.

在 Access 数据库引擎 SQL 语法中，要使用%通配符，您必须使用 ANSI-92 查询模式或使用ALIKE关键字代替LIKE关键字。

For details of ANSI-92 Query Mode see About ANSI SQL query mode (MDB)in the Access2003 Help (the same will apply to ACE in Access2007 but they removed the topic from the Access2007 Help for some reason). If you doing this in code you will need to use OLE DB e.g. ADO classic in VBA.

有关 ANSI-92 查询模式的详细信息，请参阅Access2003 帮助中的关于 ANSI SQL 查询模式 (MDB)（同样适用于 Access2007 中的 ACE，但由于某些原因他们从 Access2007 帮助中删除了该主题）。如果您在代码中执行此操作，您将需要使用 OLE DB，例如 VBA 中的 ADO classic。

For the ALIKEkeyword... you won't find much. It's one of those officially undocumented features, meaning there is an element of risk that it may be removed from a future revision to the Access database engine. Personally, I'd take that risk over having to explicitly code for both ANSI-89 Query Mode and ANSI-92 Query Mode as is necessary for Validation Rules and CHECKconstraints (see example below). Coding for both can be done but it is more long winded and tricky to get right i.e. has more immediate risk if you get it wrong.

对于ALIKE关键字...你不会找到太多。这是官方未记录的功能之一，这意味着存在风险元素，它可能会从 Access 数据库引擎的未来修订版中删除。就我个人而言，我会冒着必须为 ANSI-89 查询模式和 ANSI-92 查询模式显式编码的风险，这是验证规则和CHECK约束所必需的（参见下面的示例）。两者的编码都可以完成，但正确的方式更加冗长且棘手，即如果您弄错了，则具有更大的直接风险。

That's the answer. Now for the 'solution'...

这就是答案。现在为“解决方案”...

Clearly, if you need to perform that kind of query on emp_id then the domain is wrong i.e. it shouldn't be a numeric field.

显然，如果您需要对 emp_id 执行那种查询，那么域是错误的，即它不应该是数字字段。

Cure the disease: change the schema to make this a text field, adding a domain rule ensuring it only contains numeric characters e.g.

治愈疾病：更改架构以使其成为文本字段，添加域规则以确保它仅包含数字字符，例如

CHECK (emp_id NOT LIKE '%[^0-9]%')

EDIT the 'Jet' tag has now been added. The above CHECKconstraint needs to be rewritten because the Access database engine has its own syntax: replace the ^character with !. Also, to make this compatible with both ANSI-89 Query Mode and ANSI-92 Query Mode, use the ALIKE keyword i.e.

编辑现在已添加“Jet”标签。上述CHECK约束需要重写，因为 Access 数据库引擎有自己的语法：将^字符替换为!. 此外，为了使其与 ANSI-89 查询模式和 ANSI-92 查询模式兼容，请使用 ALIKE 关键字，即

CHECK (emp_id NOT ALIKE '%[!0-9]%')

回答by KuldipMCA

Use castor convertfunction on the emp_idfield and you can compare with like.

在现场使用cast或convert功能emp_id，您可以与like.

回答by jfraber

using CONCAT implic convert integer to string

使用 CONCAT 隐式将整数转换为字符串

SELECT * FROM city WHERE CONCAT(id_city,'') LIKE '%119%'

SELECT * FROM city WHERE CONCAT(id_city,'') LIKE '%119%'

回答by Hymanjo

Hi I had trouble doing this with a float I had to cast two tims cast(cast(EmpId as bigint) as varchar(15)) like '%903%' Hope someone find this helpful

嗨，我在使用浮点数时遇到了麻烦，我不得不将两个 tims cast(cast(EmpId as bigint) as varchar(15)) 像 '%903%' 希望有人觉得这有帮助

回答by MrWatson

In FileMaker SQL you can cast any field to a string with the STRVAL function:

在 FileMaker SQL 中，您可以使用 STRVAL 函数将任何字段转换为字符串：

SELECT * FROM emp WHERE STRVAL(emp_id) LIKE '1%'

SELECT * FROM emp WHERE STRVAL(emp_id) LIKE '1%'

---

---

Here is a real example of the use of this in FileMaker:

以下是在 FileMaker 中使用此功能的真实示例：

How to list all records which contain an ASCII 0 in a numeric field

如何列出在数字字段中包含 ASCII 0 的所有记录

SELECT emp_id FROM emp WHERE STRVAL(someNumericField) LIKE '%'+CHR(0)+'%'

SELECT emp_id FROM emp WHERE STRVAL(someNumericField) LIKE '%'+CHR(0)+'%'