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](http://stackoverflow.com/questions/16760900/pad-a-string-with-leading-zeros-so-its-3-characters-long-in-sql-server-2008) — techspider, Jan 18 '16 at 19:57

JonH · Answer 1 · 2016-01-18T20:11:32.630

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 '16 at 20:11

answered Jan 18 '16 at 20:02

JonH

32,732
12
87
145

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 – barry17 Jan 18 '16 at 20:19
@barry17 - You need to post your code. There is no code in your post. – JonH Jan 18 '16 at 20:20
@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. – JonH Jan 18 '16 at 20:35

Krishnraj Rana · Answer 2 · 2016-01-18T21:16:51.903

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 '16 at 21:16

answered Jan 18 '16 at 20:18

Krishnraj Rana

6,516
2
29
36

I need to be able to use this in a view. SQL SERVER won't let me declare any variables in a view – barry17 Jan 18 '16 at 21:06
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 – barry17 Jan 18 '16 at 21:21
@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 Jan 18 '16 at 21:25
1

@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. – Krishnraj Rana Jan 18 '16 at 21:43

Formatting in SQL Server?

2 Answers2