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