Query Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query Help

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-09-17 : 09:09:47

[code]
Declare @tempnum bigint
set @tempnum ='12345'

select 'V' + convert(varchar,Replicate('0', 7 - Len(Ltrim(Rtrim(@tempnum))))+@tempnum+ row_number() over(order by @tempnum)) as col1

Expected output:

Col1
-----
V0012345

Please correct the above query to get the desired output.

[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 11:06:26

SELECT RIGHT('V0000000' +@tempnum+ row_number() over(order by @tempnum)),8) as col1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-09-17 : 11:16:32

Thanks

But i got error saying The right function requires 2 argument(s).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 11:21:22

there was a typo


SELECT RIGHT('V0000000' +@tempnum+ row_number() over(order by @tempnum),8) as col1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-09-17 : 11:27:01

I got below error..

Msg 8114, Level 16, State 5, Line 4
Error converting data type varchar to bigint.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 11:46:50

is tempnum a varchar field?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-09-17 : 12:33:25

@tempnum bigint

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 13:04:23

cast it to varchar as well then

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-09-17 : 13:27:59

[code]
declare @tempnum bigint
set @tempnum ='12345'
select RIGHT('V0000000' +cast(@tempnum as varchar(10))+ row_number() over(order by cast(@tempnum as varchar(10))),8) as col1

I got error again..

Error converting data type varchar to bigint.

[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 14:34:06

it should be



declare @tempnum bigint
set @tempnum ='12345'
select RIGHT('V0000000' +cast(@tempnum as varchar(10))+ cast(row_number() over(order by @tempnum)as varchar(10)),8) as col1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-09-17 : 17:48:12

Thanks Visakh..

I want to auto increment the value


declare @tempnum bigint
set @tempnum ='12345'
select RIGHT('V0000000' +cast(@tempnum as varchar(10))+ cast(row_number() over(order by @tempnum)as varchar(10)),8) as col1

Expected output:
------------
V0012346

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 17:50:50

autoincrement based on what? currently you just have select some values without even a table specified so didnt understand how many values you want and on what basis you'll determine number of rows!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-09-17 : 18:15:10

I want to autoincrement the value based on the '12345' and i dont want the digits to grow more than 7 digits i.e 9999999..
I want to add leading zero's if the value has less than 5 or 6 digits..

Thanks for your help in advance..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 19:28:08

do you've a tally table?

if not use query below


;With Numbers (N)
AS
(
SELECT 12345
UNION ALL
SELECT N + 1
FROM Numbers
WHERE N + 1 <=9999999
)

SELECT RIGHT('V0000000' +cast(N as varchar(10)),8)
FROM Numbers


OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources