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".
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.
In my last question (using INTs) I was offered something similar to the following solution, and it worked great.
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?
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.
You can convert the char(1) to its ascii number using
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:
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With