blocks|key|463634|text|使用_运算符而不是%25运算符：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|463635|WHERE+vehicleId+LIKE+'R8__'+OR+vehicleId+LIKE+'8__'|code-block|syntax|javascript|463636|您也可以像这样组合它们：|463637|WHERE+vehicleId+LIKE+'%258__'|463638|entityMap^0|2|1|9|1|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|W|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|X|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|Y|8|@]|D|@]|E|$]]]|P|$]]

Use the <code>_</code> operator instead of the <code>%</code> operator:

<pre><code>WHERE vehicleId LIKE 'R8__' OR vehicleId LIKE '8__'
</code></pre>

You may also combine them like so:

<pre><code>WHERE vehicleId LIKE '%8__'
</code></pre>

blocks|key|463645|text|这样如何：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|463646|WHERE+vehicleId+BETWEEN+'800'+and+'899'+AND+LEN(vehicleId)+=+3|code-block|syntax|javascript|463647|不是完全干净，但它将消除两个字符匹配。|463648|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

How about this:

<pre><code>WHERE vehicleId BETWEEN '800' and '899' AND LEN(vehicleId) = 3
</code></pre>

Not perfectly clean, but it would eliminate the two character matches.

blocks|key|1890717|text|好的，你的问题是你正在尝试对一个字符串域进行数学排序。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1890718|尝试使用派生表之类的方法首先排除非数字，然后将结果数据转换为int|1890719|select+*+from+
(select+*+from+mytable+where+vehicleId+not+like+'R%25')a
where+cast(vehicleId+as+int)+between+800+and+900|code-block|syntax|javascript|1890720|这不起作用，你可以尝试select+*+from+(select+*+from+mytable+where+isnumeric(vehicleId)=0)a+where+cast(vehicleId+as+int)介于800和900之间|1890721|isnumeric有一些问题，但如果它可以工作，你可以编写一个isint()函数并使用它。|1890722|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

OK your problem is you are trying to do a math sort on a string field. 

try something like using a derived table to first exclude the nonnumerics and tehn cast the resultant data to int

<pre><code>select * from 
(select * from mytable where vehicleId not like 'R%')a
where cast(vehicleId as int) between 800 and 900
</code></pre>

Well that didn't work, you could try
 select * from 
 (select * from mytable where isnumeric(vehicleId)=0)a
 where cast(vehicleId as int) between 800 and 900

There are some issues with isnumeric, but if it works you could write an isint() function and use that instead.

blocks|key|3298947|text|这应该可以做到这一点，尽管它没有经过测试：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3298948|where+cast(replace(vehicleId,'R','')+as+int)+between+800+and+899|code-block|syntax|javascript|3298949|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This should do the trick although its untested:

<pre><code>where cast(replace(vehicleId,'R','') as int) between 800 and 899
</code></pre>

blocks|key|3298970|text|不能保证优化器将以正确的顺序执行这些操作(SQL中目前没有短路评估)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3298971|where+vehicleId+not+like+'R%25'+and+cast(vehicleId+as+int)+between+800+and+899+|code-block|syntax|javascript|3298972|因此，强制转换可能会失败。|3298973|试试这个：|3298974|WHERE
++++CASE
++++++++WHEN+vehicleId+NOT+LIKE+'R%25'+THEN
++++++++++++CAST(vehicleId+as+int)
++++++++ELSE
++++++++++++0
++++END+BETWEEN+800+AND+899+|3298975|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|S|8|@]|9|@]|A|$E|F]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

There is no guarantee that the optimizer will execute these in the right order (there's currently no short-circuit evaluation in SQL):

<pre><code>where vehicleId not like 'R%' and cast(vehicleId as int) between 800 and 899 
</code></pre>

So the cast can fail.

Try this:

<pre><code>WHERE
 CASE
 WHEN vehicleId NOT LIKE 'R%' THEN
 CAST(vehicleId as int)
 ELSE
 0
 END BETWEEN 800 AND 899 
</code></pre>

blocks|key|463700|text|where+1=
(
++++case+
++++++++when+
++++++++++++vehicleId+like+'R%25'+then+0+
++++++++else+
++++++++++++(case+when+cast(vehicleId+as+int)+between+800+and+899+then+1+else+0+end)+
++++end
)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|463701|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>where 1=
(
 case 
 when 
 vehicleId like 'R%' then 0 
 else 
 (case when cast(vehicleId as int) between 800 and 899 then 1 else 0 end) 
 end
)
</code></pre>

blocks|key|463712|text|我不确定您是否希望包含'R%25‘记录，也不确定vehicleIds的范围是否可变，但这可能会有所帮助。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|463713|WHERE+vehicleId+LIKE+'8[0-9][0-9]'+
OR+vehicleId+LIKE+'R8[0-9][0-9]'|code-block|syntax|javascript|463714|如果范围是一个变量，那么你可能想要这样的东西。|463715|WHERE+CASE+WHEN+vehicleId+LIKE+'R%25'+THEN+CAST(SUBSTRING(vehicleId,2,99)+AS+INT)
++++WHEN+vehicleId+LIKE+'[0-9][0-9][0-9]'+THEN+CAST(vehicleId+AS+INT)
++++END+BETWEEN+800+AND+899|463716|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

I'm not sure if you wish to include the 'R%' records and whether the range of vehicleIds is variable or not, but this might help.

<pre><code>WHERE vehicleId LIKE '8[0-9][0-9]' 
OR vehicleId LIKE 'R8[0-9][0-9]'
</code></pre>

If the range is a variable then you may want something like this.

<pre><code>WHERE CASE WHEN vehicleId LIKE 'R%' THEN CAST(SUBSTRING(vehicleId,2,99) AS INT)
 WHEN vehicleId LIKE '[0-9][0-9][0-9]' THEN CAST(vehicleId AS INT)
 END BETWEEN 800 AND 899
</code></pre>

blocks|key|1212027|text|当我处理这样的事情时，我会做这样的事情...|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1212028|DECLARE+@Autos+Table++(
ID+varchar(100)
)

Insert+into+@Autos+(ID)+values+('R1')
Insert+into+@Autos+(ID)+values+('2')
Insert+into+@Autos+(ID)+values+('3')
Insert+into+@Autos+(ID)+values+('R4')
Insert+into+@Autos+(ID)+values+('5')
Insert+into+@Autos+(ID)+values+('R52')
Insert+into+@Autos+(ID)+values+('53')
Insert+into+@Autos+(ID)+values+('R8')

Select+*+from+@Autos
Where+cast(REPLACE(ID,+'R',+'0')+as+integer)+between+2+and+55
and+ID+not+like+'R%25'|code-block|syntax|javascript|1212029|输出结果是，|1212030|ID
---
2
3
5
53|1212031|--克里斯|1212032|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

I do somthing like this when I'm dealing with things like this... 

<pre><code>DECLARE @Autos Table (
ID varchar(100)
)

Insert into @Autos (ID) values ('R1')
Insert into @Autos (ID) values ('2')
Insert into @Autos (ID) values ('3')
Insert into @Autos (ID) values ('R4')
Insert into @Autos (ID) values ('5')
Insert into @Autos (ID) values ('R52')
Insert into @Autos (ID) values ('53')
Insert into @Autos (ID) values ('R8')

Select * from @Autos
Where cast(REPLACE(ID, 'R', '0') as integer) between 2 and 55
and ID not like 'R%'
</code></pre>

Output is,

<pre><code>ID
---
2
3
5
53
</code></pre>

--Kris

I am directly querying the back-end MS SQL Server for a software package. The key field (vehicle number) is defined as alpha though we are entering numeric value in the field. There is only one exception to this, we place an "R" before the number when the vehicle is being retired (which means we sold it or the vehicle is junked). Assuming the users do this right, we should not run into a problem using this method. (Right or wrong isn't the issue here)

Fast forward to now. I am trying to query a subset of these vehicle numbers (800 - 899) for some special processing. By doing a range of '800' to '899' we also get 80, 81, etc. If I cast the vehicle number into an INT, I should be able to get the right range. Except that these "R" vehicles are kicking me in the butt now. 

I have tried <code>where vehicleId not like 'R%' and cast(vehicleId as int) between 800 and 899</code> however, I get a casting error on one of these "R" vehicles. 

What does work is <code>where vehicleId not between '800' and '899' and cast(vehicleId as int) between 800 and 899'</code>, but I feel there has to be a better way and less confusing way.

I have also tried other variations with <code>HAVING</code> and a sub-query all producing a casting error.

Fixing an SQL where statement that is ugly and confusing

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我直接向后端MS SQL Server查询软件包。关键字段(vehicle number)被定义为alpha，尽管我们在该字段中输入了数值。只有一个例外，当车辆退役时，我们在数字前面放一个"R“(这意味着我们把它卖了，或者这辆车是废弃的)。假设用户这样做是正确的，我们应该不会在使用这种方法时遇到问题。(对或错不是这里的...

问修复丑陋且令人困惑的SQL where语句
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问修复丑陋且令人困惑的SQL where语句EN