CODEWITHSUNDEEP
sqlsql-server
jay
jay

Reputation: 3879

How to fill column values length same in SQL Server 2008?

Excel Sheet data

Eno  EName  Pincode
1     aaa    123456
2     bbb    123423
3     ccc    34
4     ddd     567
5     eee     4532

Table Structure

  Eno  Varchar(10)
  EName  vachar(25)
  Pincode  Nvarchar(6)

I fetch data from excel sheet on the time pincode have 3 or 2 or 6 or 4, I have to check that If pincode column values is less than 6 i have concat 0.

 Eg: Pincode 34 it have only 2 digit so i add 4 zero in front 000034.

Result

EmpInfo

 Eno  EName   PinCode
    1    aaa      123456
    2     bbb     123423 
    3     ccc     000034
    4     ddd     000567
    5      eee    004532

Upvotes: 3

Views: 1631

Answers (3)

Alex
Alex

Reputation: 2011

I suggest you wrap this into a scalar-valued function like this:

CREATE FUNCTION FormatPinCode 
(
    @rawPin NVARCHAR(6),
    @prefix NVARCHAR(6) = '000000' /* Optional */
)
RETURNS NVARCHAR(6)
AS
BEGIN
    DECLARE @Result NVARCHAR(6)
    SELECT @Result = RIGHT(@prefix + @rawPin, 6)
    RETURN @Result
END

Use like this:

SELECT Eno, EName, FormatPinCode(PinCode)

Upvotes: 1

Michael Riley - AKA Gunny
Michael Riley - AKA Gunny

Reputation: 5184

Try this...

select 
Eno,
EName,
right('000000' + PinCode, 6) as PinCode

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

You can add 1000000 to the PinCode value and fetch the 6 rightmost letters.

select right(1000000+PinCode, 6)

Upvotes: 2

Related Questions