Home > Mobile >  SQL Server to expand rows based on alphabet letters
SQL Server to expand rows based on alphabet letters

Time:10-07

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

enter image description here

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

enter image description here

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

  • Related