Formatting in SQL Server?

Question

I'm trying to take some queries from Access and store them as VIEW in SQL Server. I have an iif statement that I'm trying to make SQL friendly. It looks like this... (40 zeros)

Format([PassNo],"0000000000000000000000000000000000000000")) AS SORTPassNo

Pass No is anywhere from 5 to 12 numbers (12345 to 123456712345). What this formatting does in Access, is it makes the entire number 40 characters, with all the zeros in front so the 5 digit number above looks like this

0000000000000000000000000000000000012345

How would I do this in SQL Server?

Here's the code... not all of it, just the GROUP BY part of it that needs fixing, Im declaring this at the top...

DECLARE @PassNo varchar(40)

GROUP BY dbo.tblVendors.VendorName
, dbo.tblCentralInv.VendorID, dbo.tblCentralInv.InvType
,CASE 
    WHEN tblcentralinv.invtype ='qty'
    THEN '0000000000000000000000000000000000000000'
    ELSE (SELECT Replicate ('0', 40 -len(@PassNo)) + @PassNo)
    END

EDIT I AM CREATING A VIEW WITH THIS CODE. I am not able to use a subquery or declare any variables

Here's all my CODE From the view

Create View [dbo].[vBegInv]
AS
Declare @ SORTInvVoucherChkPassNo varchar(40)
SELECT dbo.tblCentralVendors.VendorName
, dbo.tblCentral.VendorID
, dbo.tblCentral.InvType
, CASE 
when invtype = 'qty' 
THEN '0'
ELSE InvVoucherChkPassNo
END as invoucherchkpassnum
, Sum(dbo.tblCentral.InvQty) AS SumOfInvQty

,CASE 
WHEN tblcentral.invtype ='qty'
THEN '0000000000000000000000000000000000000000'
ELSE REPLACE(STR(@InvVoucherChkPassNo, 40), ' ', 0)
END AS SORTInvVoucherChkPassNo
, Max(dbo.tblCentral.InvValue) AS InvValue
FROM dbo.tblCentralI NNER JOIN dbo.tblCentralVendors ON     dbo.tblCentral.VendorID = dbo.tblCentralVendors.VendorID
WHERE ((dbo.tblCentral.InvTransDate)<'12/1/2015')
GROUP BY dbo.tblCentralVendors.VendorName
, dbo.tblCentral.VendorID, dbo.tblCentral.InvType
,CASE 
    WHEN tblcentral.invtype ='qty'
    THEN '0000000000000000000000000000000000000000'
    ELSE REPLACE(STR(@InvVoucherChkPassNo, 40), ' ', 0)
    END 
 HAVING (Sum(dbo.tblCentral.InvQty))> 0

Possible duplicate of Pad a string with leading zeros so it's 3 characters long in SQL Server 2008 — techspider
– techspider, Commented Jan 18, 2016 at 19:57

JonH · Accepted Answer · 2016-01-18 20:11:32Z

3

Look into TSQL's Replicate

This allows you to:

Repeats a string value a specified number of times.

So now you just need to subtract 40 from your original string. Say your string was 12345 (length is 5), you're telling SQL Server you need 35 more characters to replicate based on the parameters. The first parameter is what you want to repeat, the second parameter is how many times you want to repeat it.

Something like so:

DECLARE @MyId varchar(40)
SET @MyId = (SELECT '40')
SELECT REPLICATE('0',40-LEN(@MyId )) + @MyId

In this example we take 40 minus the length of what @MyId is (which is only 2 length) resulting in repeating 0, 38 times. Once you've written out those 0's just append the original result.

40 is arbitrary so you can make this more generic in your code if you wanted.

edited Jan 18, 2016 at 20:11

answered Jan 18, 2016 at 20:02

JonH

33.2k13 gold badges94 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

barry17 Over a year ago

Question is I'm including the SELECT REPLICATE statement in in a SELECT CASE WHEN statement, but then I also need to group by the result of the SELECT REPLICATE, however, sql won't let me use a subquery in GROUP BY

JonH Over a year ago

@barry17 - You need to post your code. There is no code in your post.

JonH Over a year ago

@barry17 - It's the SELECT Replicate in your group by that doesn't work. You can satisfy this condition by using a CTE or determine the group by outside of the query.

Krishnraj Rana · Accepted Answer · 2016-01-18 21:16:51Z

2

And how about this. Using str function.

DECLARE @MyId varchar(40) = '12345'

PRINT REPLACE(STR(@MyId, 40), ' ', 0)

Output

0000000000000000000000000000000000012345

You can use your column instead of variable. You can create view with this function like this

CREATE VIEW MyView
AS
    SELECT PassNo, REPLACE(STR(PassNo, 40), ' ', 0) as PassNoWithLeadingZero
    FROM tbl

edited Jan 18, 2016 at 21:16

answered Jan 18, 2016 at 20:18

Krishnraj Rana

6,6583 gold badges33 silver badges37 bronze badges

4 Comments

barry17 Over a year ago

I need to be able to use this in a view. SQL SERVER won't let me declare any variables in a view

barry17 Over a year ago

Thank you. I need to use the PASSnoWithLeadingZero value in a group by, and it won't let me use a subquery in GROUP BY

Krishnraj Rana Over a year ago

@barry17: Could you please show me your query and also the code of your view. Just edit your question. One more thing please don't put half of the code.

Krishnraj Rana Over a year ago

@barry17: Remove this variable declaration from your view - Declare @SORTInvVoucherChkPassNo varchar(40) and use column name in str function not variable. like this - REPLACE(STR(InvVoucherChkPassNo, 40), ' ', 0). Another thing is remove all cases from your view. Make it simple. Just select required column in your view and make case when you actually retrieve column from your view. Hope you got it what i mean to say.

Collectives™ on Stack Overflow

Formatting in SQL Server?

2 Answers 2

3 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related