使用TSQL,我可以将CHAR(1)列递增1并在没有CASE语句的LEFT OUTER JOIN中使用它吗?

时间:2022-06-23 15:34:15

This question is similar to my last question. Except this time I'm using letters rather than 6 digit integers. I want to find the out of sequence "letters".

这个问题类似于我的上一个问题。除了这次我使用的是字母而不是6位整数。我想找到不按顺序的“字母”。

Let's say I have the following data:

假设我有以下数据:

id | Date | Letter
-----------------------------
01 | 5/1/2009 | X
02 | 5/1/2009 | Y
03 | 5/1/2009 | Z
04 | 5/1/2009 | A
05 | 5/1/2009 | B
06 | 5/1/2009 | D

I would like to be able to come up with a query that would tell me there should be a row with "C" in between row 05 and 06.

我希望能够提出一个查询,告诉我在05和06行之间应该有一行“C”。

In my last question (using INTs) I was offered something similar to the following solution, and it worked great.

在我的上一个问题(使用INT)中,我获得了类似于以下解决方案的东西,并且它工作得很好。

SELECT * from TABLE1 t1 
LEFT OUTER JOIN TABLE2 t2 ON t2.INTCol - 1 = t2.INTCol AND t1.date = t2.date
WHERE t2.id IS NULL

Well, with letters (as far as I know) I can't say (G - 1). So, is there another way I can do this?

好吧,有了字母(据我所知),我不能说(G - 1)。那么,我还能采用另一种方式吗?

The database I am using is SQL Server 2005. I believe there is an easy solution in PL/SQL that uses TRANSLATE, but I don't thing I can do anything like using TSQL.

我使用的数据库是SQL Server 2005.我相信在使用TRANSLATE的PL / SQL中有一个简单的解决方案,但我不能做任何事情,比如使用TSQL。

2 个解决方案

#1


11  

You can convert the char(1) to its ascii number using

您可以使用将char(1)转换为其ascii数字

ASCII(Letter)

You can then increment this by one and return it to a letter using CHAR (if necessary), so your code would be this:

然后,您可以将其递增1并使用CHAR(如有必要)将其返回到字母,因此您的代码将是:

SELECT * from TABLE1 t1 
LEFT OUTER JOIN TABLE2 t2 
      ON ASCII(t1.INTCol) - 1 = ASCII(t2.INTCol) 
      AND t1.date = t2.date
WHERE t2.id IS NULL

#2


4  

You can use the ASCII() function to convert a character to its ASCII value:

您可以使用ASCII()函数将字符转换为其ASCII值:

ASCII(Letter)

ASCII(信)

#1


11  

You can convert the char(1) to its ascii number using

您可以使用将char(1)转换为其ascii数字

ASCII(Letter)

You can then increment this by one and return it to a letter using CHAR (if necessary), so your code would be this:

然后,您可以将其递增1并使用CHAR(如有必要)将其返回到字母,因此您的代码将是:

SELECT * from TABLE1 t1 
LEFT OUTER JOIN TABLE2 t2 
      ON ASCII(t1.INTCol) - 1 = ASCII(t2.INTCol) 
      AND t1.date = t2.date
WHERE t2.id IS NULL

#2


4  

You can use the ASCII() function to convert a character to its ASCII value:

您可以使用ASCII()函数将字符转换为其ASCII值:

ASCII(Letter)

ASCII(信)