Skip a letter in the alphabet in SQL query

jnordeng
jnordeng used Ask the Experts™
on
Hi I was asked to come up with code to convert letters of the alphabet to months of the year which I did no problem. But then they  came back to me and said I needed to skip the letter "I"  in the process. I am stumped. If anyone can help I would sure appreciate it.

Data is 7115A12345. "A" is the month in this serial number that we want to convert to the month number but we need to skip "I" in the process

(ASCII(SUBSTRING(serial_number,5,1))- ASCII('A') + 1)  as [MONTH]
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
CHARINDEX(SUBSTRING(serial_number,5,1), 'ABCDEFGHJKLM') AS [MONTH]
Professional Troublemaker^h^h^h^h^hshooter
Commented:
CASE
   WHEN ASCII(SUBSTRING(serial_number,5,1)) < 73 THEN (ASCII(SUBSTRING(serial_number,5,1))- ASCII('A') + 1)
   WHEN ASCII(SUBSTRING(serial_number,5,1)) =73 THEN --appropriate Error response
   ELSE (ASCII(SUBSTRING(serial_number,5,1))- ASCII('A') )
END as [MONTH]

Open in new window


This makes H=August, J=September, etc... 'I' is skipped.
You can skip the =73 case if you like... I'd included it in my testing so I could see that something happened to 'I'.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial