Append Characters using built-in formula while column fetch in SQL Server

Question

I have been given a scenario that to select 4 characters from a given cell(the last 4)

I am doing it manually by using Case expression. I need to automate it rather using Cases

I have done this.

SELECT CASE WHEN LEN(ticket_number) >= 4 THEN
              'x' + RIGHT(ticket_number,4)
            WHEN LEN(ticket_number) = 3 THEN 
              'x0' + ticket_number
            WHEN LEN(ticket_number) = 2 THEN 
              'x00' + ticket_number
            WHEN LEN(ticket_number) = 1 THEN 
              'x000' + ticket_number
            ELSE
              ''
        END AS ticket_number
FROM tickets
WHERE ticket_number <> ''
ORDER BY date DESC

I need to optimize this query.

Answer 1

You mean like this?

'x' + RIGHT('000'+ticket_number,4) AS ticket_number

If I hard-code a 2-character value:

SELECT 'x' + RIGHT('000'+'12',4) AS ticket_number

I get:

x0012

So the solution definitely works.