Comments and answers for "Dynamic varchar variable"https://ask.sqlservercentral.com/questions/51975/dynamic-varchar-variable.html
The latest comments and answers for the question "Dynamic varchar variable"Comment by WilliamD on WilliamD's answerhttps://ask.sqlservercentral.com/comments/51983/view.html
Woops, you're right. I'll change that now.Fri, 27 Jan 2012 03:05:19 GMTWilliamDComment by robbin on robbin's answerhttps://ask.sqlservercentral.com/comments/51982/view.html
Seems like OP asked for only 5 characters long string ;) But it fulfills the core requirement though.Fri, 27 Jan 2012 03:03:06 GMTrobbinAnswer by Håkan Wintherhttps://ask.sqlservercentral.com/answers/51978/view.html
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 BThu, 26 Jan 2012 23:58:47 GMTHåkan WintherAnswer by Usman Butthttps://ask.sqlservercentral.com/answers/51977/view.html
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 @DynamicThu, 26 Jan 2012 23:50:13 GMTUsman ButtAnswer by WilliamDhttps://ask.sqlservercentral.com/answers/51976/view.html
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.Thu, 26 Jan 2012 23:49:27 GMTWilliamD