x

Dynamic varchar variable

I want to create a dynamic varchar variable like 'B0001'. if the last charcater of the variable exceeds 9 then the variable should look like 'B0010' and if the last character exceeds 99 the variable should look like 'B0100' and so on. Means the length of the variable should be fixed i.e 5 and the last number should be auto incremental like 'B0008','B0009','B0010' ,'B0011' and so on. Also if the last three digits exceeds 99 the variable should look like 'B0099','B0100','B0101' and so on

more ▼

asked Jan 26, 2012 at 11:38 PM in Default

avatar image

anujahlawat
2 2 2 2

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

It seems to me that you are just wanting to return an integer with leading zeroes to form a 6 character string.

Something like this would achieve what you want:

 DECLARE @i int
 SET @i = 10
 
 SELECT  'B' + RIGHT('0000' + CAST(@i AS varchar(50)), 4)

You could wrap this into a function to make the code re-usable and also allow for different lengths etc. This should put you on the right track though.

more ▼

answered Jan 26, 2012 at 11:49 PM

avatar image

WilliamD
26.2k 18 34 48

Seems like OP asked for only 5 characters long string ;) But it fulfills the core requirement though.

Jan 27, 2012 at 03:03 AM robbin

Woops, you're right. I'll change that now.

Jan 27, 2012 at 03:05 AM WilliamD
(comments are locked)
10|1200 characters needed characters left

You need to let us know how the implementation would be? If you would pass an integer then it could be done as

 DECLARE @Dynamic CHAR(5), @Int SMALLINT
 
 SET @Int = 1
 
 SELECT @Dynamic = 'B' + RIGHT('0000'+ CAST(@Int AS VARCHAR(50)),4)
 
 SELECT @Dynamic
 
 SET @Int = 101
 
 SELECT @Dynamic = 'B' + RIGHT('0000'+ CAST(@Int AS VARCHAR(50)),4)
 
 SELECT @Dynamic
 
 SET @Int = 1010
 
 SELECT @Dynamic = 'B' + RIGHT('0000'+ CAST(@Int AS VARCHAR(50)),4)
 
 SELECT @Dynamic
more ▼

answered Jan 26, 2012 at 11:50 PM

avatar image

Usman Butt
13.9k 6 13 21

(comments are locked)
10|1200 characters needed characters left

You can use stuff to solve the problem, but you should reconsider if you really should do it. A varchar(5) takes 7 bytes and an integer only takes 4 bytes and that may affect the performance.

 SELECT STUFF('B0000', 6-LEN(b),LEN(b),b) b FROM (VALUES('1'),('2'),('1003'),('12')) AS a(b)
 ORDER BY B
more ▼

answered Jan 26, 2012 at 11:58 PM

avatar image

Håkan Winther
16.5k 36 45 57

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x32

asked: Jan 26, 2012 at 11:38 PM

Seen: 1403 times

Last Updated: Jan 26, 2012 at 11:38 PM

Copyright 2016 Redgate Software. Privacy Policy