CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Shaggy
Shaggy

Reputation: 5790

Left pad varchar to certain length in sql server

I want to left pad int number with 12 digits as 0 and starting with character as 'P'

E.g if number is 345 Then output should be 'P00000000345'

My Code :

Declare @a int
Set @a =8756
Select Right('P00000000000' +Cast(@a As Varchar(11)),12)

DB : SQL SERVER 2008

Upvotes: 0

Views: 6102

Answers (5)

AitchTee
AitchTee

Reputation: 1

This will left pad the number with zeros. 11 zeros and prepend the 'P', so a total of 12 chars.

declare @a int
set @a = 234
select @a as ORIG, 'P' || str(@a, 11, '0') as CHAR12_STRING

Upvotes: 0

Satpal
Satpal

Reputation: 133403

Try this

Declare @a int
Set @a =8756
Select 'P' + REPLACE(STR(@a, 11), SPACE(1), '0')

Demo: http://sqlfiddle.com/#!3/d41d8/18547

Upvotes: 2

podiluska
podiluska

Reputation: 51494

Try

Declare @a int
Set @a =8756
Select 'P'+Right('00000000000' +Cast(@a As Varchar(11)),11)

Upvotes: 4

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239694

You're mostly correct, but should apply the P as a separate step:

Declare @a int
Set @a =8756
Select 'P' + Right('000000000000' +Cast(@a As Varchar(11)),12)

Upvotes: 2

Adriaan Stander
Adriaan Stander

Reputation: 166396

Change

Select Right('P00000000000' +Cast(@a As Varchar(11)),12)

to

Select 'P' + Right('00000000000' +Cast(@a As Varchar(11)),11)

SQL Fiddle DEMO

Upvotes: 1

Related Questions