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 '12 at 11:38 PM in Default

anujahlawat gravatar image

anujahlawat
2 1 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 '12 at 11:49 PM

WilliamD gravatar image

WilliamD
25.8k 17 19 41

Seems like OP asked for only 5 characters long string ;) But it fulfills the core requirement though.
Jan 27 '12 at 03:03 AM robbin
Woops, you're right. I'll change that now.
Jan 27 '12 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 '12 at 11:50 PM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(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 '12 at 11:58 PM

Håkan Winther gravatar image

Håkan Winther
15.5k 33 37 48

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x19

asked: Jan 26 '12 at 11:38 PM

Seen: 939 times

Last Updated: Jan 26 '12 at 11:38 PM