CODEWITHSUNDEEP
sqlsql-serversql-server-2008
no9
no9

Reputation: 6544

How to change column type in SQL view

Maybe sounds like a dumb question but here is my situation:

I have a table with integer type field (name PartnerID). I need to create a view on this table and adds (actually replaces) leading zeros to the integer field.

Example for field (PartnerID) 11235 11567 11567 22789 22657 22456 33567

Should be (in view): 00235 00567 00567 00789 00657 00456 33567

Since thats impossible what are my options?

EDIT: Im not adding zeros im replacing.

Here is the current formula for the field PartnerID in the view, but this just ignores the leading zeros. I want to have leading zeros based on this formula in my view.

CASE WHEN (LEN(PartnerID) = 5 AND (PartnerID LIKE '11%' OR
                      PartnerID LIKE '22%')) THEN stuff(PartnerID, 1, 2, '00') ELSE PartnerId END AS PartnerID

Regards

Upvotes: 0

Views: 1146

Answers (2)

James Osborn
James Osborn

Reputation: 1275

Try converting the Integer PartnerId to a string e.g:

CASE WHEN (LEN(PartnerID) = 5 AND (PartnerID LIKE '11%' OR
                      PartnerID LIKE '22%')) THEN stuff(PartnerID, 1, 2, '00') ELSE CONVERT(VARCHAR(5), PartnerId) END AS PartnerID

Upvotes: 1

Ash Burlaczenko
Ash Burlaczenko

Reputation: 25445

What makes you think this is not possible?

Would something like this not work

SELECT RIGHT('0000'+ CONVERT(VARCHAR,IntegerColumn), 5) FROM YourTable

Upvotes: 0

Related Questions