SQL: Is there a possibility to convert numbers (1,2,3,4...) to letters (A,B,C,D...)
Try this:
SELECT
Letters = Char(64 + T.Num),
T.Col1,
T.Col2
FROM
dbo.YourTable T
;
Just be aware that when you get to 27 (past Z
), things are going to get interesting, and not useful.
If you wanted to start doubling up letters, as in ... X, Y, Z, AA, AB, AC, AD ...
then it's going to get a bit trickier. This works in all versions of SQL Server. The SELECT
clauses are just an alternate to a CASE statement (and 2 characters shorter, each).
SELECT
*,
LetterCode =
Coalesce((SELECT Char(65 + (N.Num - 475255) / 456976 % 26) WHERE N.Num >= 475255), '')
+ Coalesce((SELECT Char(65 + (N.Num - 18279) / 17576 % 26) WHERE N.Num >= 18279), '')
+ Coalesce((SELECT Char(65 + (N.Num - 703) / 676 % 26) WHERE N.Num >= 703), '')
+ Coalesce((SELECT Char(65 + (N.Num - 27) / 26 % 26) WHERE N.Num >= 27), '')
+ (SELECT Char(65 + (N.Num - 1) % 26))
FROM dbo.YourTable N
ORDER BY N.Num
;
See a Live Demo at SQL Fiddle
(Demo for SQL 2008 and up, note that I use Dense_Rank()
to simulate a series of numbers)
This will work from A
to ZZZZZ
, representing the values 1
to 12356630
. The reason for all the craziness above instead of a more simple expression is because A
doesn't simply represent 0
, here. Before each threshold when the sequence kicks over to the next letter A
added to the front, there is in effect a hidden, blank, digit--but it's not used again. So 5 letters long is not 26^5 combinations, it's 26 + 26^2 + 26^3 + 26^4 + 26^5!
It took some REAL tinkering to get this code working right... I hope you or someone appreciates it! This can easily be extended to more letters just by adding another letter-generating expression with the right values.
Since it appears I'm now square in the middle of a proof-of-manliness match, I did some performance testing. A WHILE
loop is to me not a great way to compare performance because my query is designed to run against an entire set of rows at once. It doesn't make sense to me to run it a million times against one row (basically forcing it into virtual-UDF land) when it can be run once against a million rows, which is the use case scenario given by the OP for performing this against a large rowset. So here's the script to test against 1,000,000 rows (test script requires SQL Server 2005 and up).
DECLARE
@Buffer varchar(16),
@Start datetime;
SET @Start = GetDate();
WITH A (N) AS (SELECT 1 FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) A (N)),
B (N) AS (SELECT 1 FROM A, A X),
C (N) AS (SELECT 1 FROM B, B X),
D (N) AS (SELECT 1 FROM C, B X),
N (Num) AS (SELECT Row_Number() OVER (ORDER BY (SELECT 1)) FROM D)
SELECT @Buffer = dbo.HinkyBase26(N.Num)
FROM N
;
SELECT [HABO Elapsed Milliseconds] = DateDiff( ms, @Start, GetDate());
SET @Start = GetDate();
WITH A (N) AS (SELECT 1 FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) A (N)),
B (N) AS (SELECT 1 FROM A, A X),
C (N) AS (SELECT 1 FROM B, B X),
D (N) AS (SELECT 1 FROM C, B X),
N (Num) AS (SELECT Row_Number() OVER (ORDER BY (SELECT 1)) FROM D)
SELECT
@Buffer =
Coalesce((SELECT Char(65 + (N.Num - 475255) / 456976 % 26) WHERE N.Num >= 475255), '')
+ Coalesce((SELECT Char(65 + (N.Num - 18279) / 17576 % 26) WHERE N.Num >= 18279), '')
+ Coalesce((SELECT Char(65 + (N.Num - 703) / 676 % 26) WHERE N.Num >= 703), '')
+ Coalesce((SELECT Char(65 + (N.Num - 27) / 26 % 26) WHERE N.Num >= 27), '')
+ (SELECT Char(65 + (N.Num - 1) % 26))
FROM N
;
SELECT [ErikE Elapsed Milliseconds] = DateDiff( ms, @Start, GetDate());
And the results:
UDF: 17093 ms
ErikE: 12056 ms
Original Query
I initially did this a "fun" way by generating 1 row per letter and pivot-concatenating using XML, but while it was indeed fun, it proved to be slow. Here is that version for posterity (SQL 2005 and up required for the Dense_Rank
, but will work in SQL 2000 for just converting numbers to letters):
WITH Ranks AS (
SELECT
Num = Dense_Rank() OVER (ORDER BY T.Sequence),
T.Col1,
T.Col2
FROM
dbo.YourTable T
)
SELECT
*,
LetterCode =
(
SELECT Char(65 + (R.Num - X.Low) / X.Div % 26)
FROM
(
SELECT 18279, 475254, 17576
UNION ALL SELECT 703, 18278, 676
UNION ALL SELECT 27, 702, 26
UNION ALL SELECT 1, 26, 1
) X (Low, High, Div)
WHERE R.Num >= X.Low
FOR XML PATH(''), TYPE
).value('.[1]', 'varchar(4)')
FROM Ranks R
ORDER BY R.Num
;
See a Live Demo at SQL Fiddle
How can I convert numbers into letters?
char
is actually int
, you can use -
and +
between char
and int
:
// 1 - a; 26 - z
public static char convertToLetter(int num) {
return (char)('a' - 1 + num);
}
How to convert an ordinal to Excel A1 notation in TSQL
My original answer was buggy. Updated with a functionalised version of the answer from charles-bretana
that this Q/A turned out to be a duplicate of.
-- Based off http://stackoverflow.com/a/16995299/1141876
CREATE FUNCTION dbo.OrdinalToExcelA1
(
@value INT
)
RETURNS VARCHAR(10) AS
BEGIN
DECLARE @Output VARCHAR(10)
select @Output =
CASE
WHEN @value < 703 THEN ''
ELSE CHAR(64 + ((@value-26) / 26 / 26))
END
+
CASE
WHEN @value < 27 THEN ''
WHEN @value < 703 Then Char(64 + ((@value-1)/ 26))
ELSE CHAR(65 + ((@value-1)% 702 / 26))
END
+
CHAR(65 + ((@value - 1) % 26))
RETURN @Output
END
GO
Here is the code to test
;WITH Nums AS
(
SELECT n = ROW_NUMBER() OVER (ORDER BY [object_id])
FROM sys.all_objects
)
select n AS ColumnOrdinal, dbo.OrdinalToExcelA1(n) as ExcelA1Format
FROM Nums
WHERE n BETWEEN 1 AND 100
ORDER BY n;
and here is how to use it
SELECT dbo.OrdinalToExcelA1(ORDINAL_POSITION) AS ExcelColumn
,Column_Name AS Name
FROM information_schema.columns
WHERE table_name = 'MY-VIEW-NAME'
Convert a letter into a number
McNets' comment seems to be a very good approach...
If you can be sure, that you have
- plain ASCII characters
- Not more than 4 letters
You might cast the string to VARBINARY(4)
and cast this to INT
:
DECLARE @dummy TABLE(StrangeCode VARCHAR(10));
INSERT INTO @dummy VALUES
('AAAA'),('MMMM'),('ACAC'),('CDEF'),('ABCD');
SELECT CAST(CAST(StrangeCode AS VARBINARY(4)) AS INT)
FROM @dummy;
The result
1094795585
1296911693
1094926659
1128547654
1094861636
If you need bigger number, you might go up to BIGINT
SQL order string as number
If possible you should change the data type of the column to a number if you only store numbers anyway.
If you can't do that then cast your column value to an integer
explicitly with
select col from yourtable
order by cast(col as unsigned)
or implicitly for instance with a mathematical operation which forces a conversion to number
select col from yourtable
order by col + 0
BTW MySQL converts strings from left to right. Examples:
string value | integer value after conversion
--------------+--------------------------------
'1' | 1
'ABC' | 0 /* the string does not contain a number, so the result is 0 */
'123miles' | 123
'$123' | 0 /* the left side of the string does not start with a number */
Create a sequence between two letters
This would be another base R option:
letters[(letters >= "b") & (letters <= "f")]
# [1] "b" "c" "d" "e" "f"
How to split a comma-separated value to columns
CREATE FUNCTION [dbo].[fn_split_string_to_column] (
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @out_put TABLE (
[column_id] INT IDENTITY(1, 1) NOT NULL,
[value] NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @value NVARCHAR(MAX),
@pos INT = 0,
@len INT = 0
SET @string = CASE
WHEN RIGHT(@string, 1) != @delimiter
THEN @string + @delimiter
ELSE @string
END
WHILE CHARINDEX(@delimiter, @string, @pos + 1) > 0
BEGIN
SET @len = CHARINDEX(@delimiter, @string, @pos + 1) - @pos
SET @value = SUBSTRING(@string, @pos, @len)
INSERT INTO @out_put ([value])
SELECT LTRIM(RTRIM(@value)) AS [column]
SET @pos = CHARINDEX(@delimiter, @string, @pos + @len) + 1
END
RETURN
END
Related Topics
Table in Excel from SQL Server Stored Procedure with Parameter Field in Workbook
How to Get Week Start and End Date String in Postgresql
How to List User Defined Types in a SQL Server Database
How to Count Rows That Have the Same Values in Two Columns (Sql)
Concatenate/Merge Array Values During Grouping/Aggregation
Postgresql Not Ilike Clause Does Not Include Null String Values
Sequentially Number Rows by Keyed Group in SQL
Sqlite: Preventing Duplicate Rows
Need a Tool to Automatically Indent and Format SQL Server Stored Procedures
Postgresql Recursive Self Join
How to Speed Up Row_Number in Oracle
Custom Post Type Yearly/ Monthly Archive
Why Can't I Use "Create Schema" in a Begin/End Block in SQL Management Studio
How to Add a Variable Number of Hours to a Date in Postgresql
How to Copy a Table Schema and Constraints to a Table of Different Database