如果输入值介于aa和a9之间，请选择true或false [英] Select true or false if the input value is in between aa and a9

问题描述

我有一个要求，当用户输入的值位于AA和Ä9之间时，我需要将true或false放入数据库。

此处AA到A9表示.. AA，AB，AC，AD .... AZ，A1，A2，.... A9。



如何在Tsql中实现这一点而不是写所有使用IN语句可能的值？







提前致谢。



我尝试了什么：



在运营商与AA和A9之间尝试，但它没有'运行。

I have a requirement wherein I need to put true or false in to the database when the user entered value lies in between "AA" and "Ä9".
here AA to A9 means.. AA, AB, AC, AD.... AZ, A1, A2,.... A9.

How to achieve this in Tsql instead of writing all the possible values with the IN statement?



Thanks in advance.

What I have tried:

Tried between operator with with AA and A9, but it didn't work out.

推荐答案

Quote：

在运营商与AA之间尝试A9，但它没有用。

Tried between operator with with AA and A9, but it didn't work out.

当然它不起作用，因为你必须找到你的字符串是否是两个不同集合的联合，即 {'AA'，'AB'，..'AZ'} 和 {'A1'，'A2'，..，'A9'} 。

没有使用IN语句：

That's without using the IN statement:

DECLARE @InputValue AS NVARCHAR(50) = 'AZ'

SELECT CASE
		WHEN @InputValue LIKE 'A_' THEN 1
		ELSE 0
	   END

其他方式如果你想使用IN语句只有2个选项：

1.指定所有值

2.使用子查询使用IN br />

Other way if you want use the IN statement there are only 2 option:
1.Specify all value
2.Use IN with a sub query

SELECT 1
FROM [tabelname] 
WHERE [columns] IN (SELECT SalesReasonKey FROM DimSalesReason);

这个解决方案已被标记下来，不知道为什么会这样。

也许是因为使用模式匹配技术找到所需结果的解决方案。



无论如何，这将使用模式匹配技术而不是范围条件来获得您想要的结果。

-----

在你的问题中，你已经声明要查明两个字符是否在两个范围之间。

This solution has been marked down, not sure why this would be be case.
Maybe it is due to the solution using a pattern matching technique to find your desired result.

In any case this will get you your desired result using pattern matching technique and not a range condition.
-----
In your question you have stated you want to find out if two character are between two ranges.

But you have stated 3 different ranges:
aa to a9
AA to Ä9
AA to A9

您正在混合您的案例，口音和敏感度y。

整理和Unicode支持 [ ^ ]



要回答你的问题，可以考虑AA到A9。

你可以使用PATINDEX和collat​​ion Latin1_General_CS_AS来获得你想要的结果。



这里有一些sql要演示（还包括一些其他整理的结果）：

You are mixing your case, accent and sensitivity.
Collation and Unicode Support[^]

To answer your question lets consider AA to A9.
You can use PATINDEX with collation Latin1_General_CS_AS to get you desired result.

Here is a bit of sql to demonstrate (also includes results for some other collation):

with ATab as (
--setup dummy data
	select 'A1' input
	union all select 'a1'
	union all select 'Ä1'
	union all select 'b1'
	union all select 'ba'
	union all select 'AZ'
	union all select 'A9'
	union all select 'A9A'
	union all select 'BA1'
)
select 
	Input,
	patindex('A[A-Z,0-9]', Input COLLATE Latin1_General_CS_AS) PatternPosition,
	'Latin1_General_CS_AS' CollationUsed
from ATab

union all
select 
	Input,
	patindex('A[A-Z,0-9]', Input COLLATE Latin1_General_CS_AI) PatternPosition,
	'Latin1_General_CS_AI' CollationUsed
from ATab
union all
select 
	Input,
	patindex('A[A-Z,0-9]', Input COLLATE Latin1_General_CI_AS) PatternPosition,
	'Latin1_General_CI_AS' CollationUsed
from ATab
union all
select 
	Input,
	patindex('A[A-Z,0-9]', Input COLLATE Latin1_General_CI_AI) PatternPosition,
	'Latin1_General_CI_AI' CollationUsed
from ATab
;

希望能帮助你。



也可以阅读PATINDEX的这些内容：

PATINDEX（Transact-SQL） [ ^ ]

使用SQL Server的CHARINDEX和PATINDEX [ ^ ]

这篇关于如果输入值介于aa和a9之间，请选择true或false的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！