Generate a list with string prefix in SQL with fixed length

Question

I just want to generate a list like this

XY0001  
XY0002  
XY0003

The prefix is same for all rows. Need fixed length (6 in this example)
Looking for an easy way to produce such list to put it into temp table.

MS SQL

for a very small number this would do:

DECLARE @TempList TABLE (Name VARCHAR(100));  
insert into @TempList Values ('XY00001')   
insert into @TempList Values ('XY00002')   
insert into @TempList Values ('XY00003')   
insert into @TempList Values ('XY00004')   


select * from @TempList

please tag the dbms you are using and also clarify what you mean by list — Vamsi Prabhala
– Vamsi Prabhala, Commented Mar 12, 2017 at 17:07

John Cappelletti · Accepted Answer · 2017-03-12 17:53:11Z

1

You can use an ad-hoc tally table

If 2012+

DECLARE @TempList TABLE (Name VARCHAR(100)); 

Select Name = 'XY'+format(N,'0000')
 From (Select Top 9999 N=Row_Number() Over (Order By (Select NULL)) From master..spt_values N1,master..spt_values N2) A
 Order by N

Returns

Name
XY0001
XY0002
XY0003
XY0004
...
XY9997
XY9998
XY9999

If not

DECLARE @TempList TABLE (Name VARCHAR(100));

Select Name = 'XY'+right('00000'+cast(N as varchar(25)),4)
 From (Select Top 9999 N=Row_Number() Over (Order By (Select NULL)) From master..spt_values N1,master..spt_values N2) A
 Order by N

edited Mar 12, 2017 at 17:53

answered Mar 12, 2017 at 17:44

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Volker Over a year ago

The first Approach works immediately and is pretty short, which is good in my opinion. Only disadvanatge is, that I don't understand ;-)

John Cappelletti Over a year ago

@Volker Just run the sub-query, you'll see that it just generates a list of numbers. A tally/numbers table will do the trick as well. --- Happy it helped

Brandon · Accepted Answer · 2017-03-12 18:03:11Z

0

I like to use recursive CTE's for this.

declare @max_number int = 1000;

with num as (
  select 1 as n
  union
  select n + 1
  from num
  where n < @max_number
)
select 'XY' + (cast n as char(4))
from num;

The recursive CTE gives you the numbers and the cast does the left-padding with 0's to ensure you get 0001 instead of 1.

This approach will support a variable number of outputs. Though as you alluded to in your question, this is overkill if you only want a few.

(You'll need to test this out for boundary cases. I haven't tested this exact code sample.)

There is likely a limit to how far this scales because it uses recursion.

answered Mar 12, 2017 at 18:03

Brandon

10.1k3 gold badges31 silver badges40 bronze badges

1 Comment

Volker Over a year ago

This one Looks very good to me and I could adjust it with my knownledge to my Situation. Unfortunately it does not work (Syntax error) ;-(

Collectives™ on Stack Overflow

Generate a list with string prefix in SQL with fixed length

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related