如果输入值介于aa和a9之间，请选择true或false

问题描述

我有一个要求，当用户输入的值位于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'} 。

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);

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

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

[]



要回答你的问题，可以考虑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的这些内容：

[]

[]

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