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.

Tab Alleman · Accepted Answer · 2015-05-12T13:36:20.890

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.

edited May 12 '15 at 13:36

answered May 12 '15 at 13:06

Tab Alleman

31,483
7
36
52

1

I don't think you need to truncate the ticket number (assuming his CASE was comprehensive, and ticket_number Max length is 4). The outer RIGHT should be enough. – Pittsburgh DBA May 12 '15 at 13:11
1

On second thought, you're right. Even if ticket_number is more than 4 characters, the outer RIGHT is enough. – Tab Alleman May 12 '15 at 13:13
For ticket_number length less then 4 its not working. – DareDevil May 12 '15 at 13:16
@Tab Alleman Thanks, I was using same Length condition there. Thanks I have done It. – DareDevil May 12 '15 at 13:35

score 1 · Answer 2 · answered May 12 '15 at 14:26

1

select tic + REPLICATE(0,4-len(tic)) from tick

answered May 12 '15 at 14:26

sharmilamurugan

11
1

A description for even simple answers usually makes them more useful and they are generally better received. – JoelC May 12 '15 at 14:36

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

2 Answers2