SQL Server to expand rows based on alphabet letters-CodePudding

I have a table in SQL Server that looks like this:

I wonder if there is a way I can expand this table into the following through T-SQL?

I have searched around almost all the discussion is about expand date or numeric values. I wonder if there is a way to expand by alphabetical letters? Thanks!

I have tried do comparison of letters in SQL, something like this, T-SQL would recognize the order of the letters:

DECLARE @code varchar(3) = 'G0C'
DECLARE @start varchar(3) = 'G0W'
DECLARE @end varchar(3) = 'G0Z'

SELECT 
    CASE 
        WHEN @code >= @Start AND @code <= @end 
            THEN 'True' 
            ELSE 'False' 
    END

However, if I want to do increment of 1 for the letters, it won't allow varchar to 1. I thought about convert to ASCII and then convert it back but would that impact performance dramatically (the table before expand is about 3000 rows)?

CodePudding user response：

You can use an inline tally table

SELECT
  CODE = LEFT(t.[FROM], 2)   v.chr,
  t.ZONE
FROM YourTable t
JOIN (VALUES
    ('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I'),('J'),('K'),('L'),('M'),('N'),('O'),('P'),('Q'),('R'),('S'),('T'),('U'),('V'),('W'),('X'),('Y'),('Z')
) v(chr) ON v.chr BETWEEN SUBSTRING(t.[FROM], 3, 1) AND SUBSTRING(t.TO, 3, 1)

This only deals with the last character. Doing this for the whole strgin is more complex