How do I add format function, to get 'S01E02' ?
The following executes S8E72.
select
title, concat('S', season, 'E', episode) as 'episode'
from GameOfThrones
I've tried with
select FORMAT(episode, '00')
Add a comment
|
2 Answers
You would seem to want:
select title, concat('S', format(season, '00'), 'E', format(episode, '00')) as episode
from GameOfThrones2;
Note: Use single quotes only for string and date constants. Do not use single quotes for column aliases.
4 Comments
l14045
Right, I just started exploring SQL so really appreciate your comment! Thanks!
demo
Can you tell more about your note or just some link to read? Why it isn't recommended? Thx
Thom A
Gordon is saying the same thing I am in my answer, @demo . Single quotes (
') are normally for use for Literal Strings, using them for other things, like aliases, can be confusing. For example SELECT ID AS 'MyID' FROM dbo.MyTable ORDER BY 'MyID'; would not sort the data in the order of MyID, but what ever arbitrary order SQL Server felt like, as the latter 'MyID' would be interpreted as a literal string, not the alias of the same definition.demo
@Larnu, oh, that's clear about
ORDER BY :) I thought about it isn't recommending use of single quotes... at all... need to use [ ] instead... Ok, thxI would, personally, use RIGHT over FORMAT. FORMAT isn't a particularly performant function, and for much larger datasets could make your query really slow. AS a result you could do something like this:
SELECT 'S' + RIGHT(CONCAT('00',Season),2) + 'E' + RIGHT(CONCAT('00',Episode),2) AS Episode --Don't use single quote for aliases, it's confusing as single quotes are for literal strings
FROM dbo.YourTable --Stick to the dialects delimit identify or ANSI's double quotes (")
