Format number in text field with leading zeros

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Format number in text field with leading zeros

Author

Topic

sportsguy
Starting Member

39 Posts

Posted - 2012-10-29 : 15:44:49

SQLServer 2012, studio version 11

I want to format an integer in a text field as an number with leading zeros in a text field. I just can't get the query to compile properly, with error message "Msg 195, Level 15, State 10, Line 3
'format' is not a recognized built-in function name." I am not sure why I am getting that error on 2012, or how to get around the issue.
without the format line, the query works fine.


SELECT hdr.ID
	,hdr.CONTRACT_NUMBER
	,FORMAT(hdr.CONTRACT_NUMBER,'00000000') AS 'CONTRACT_NBR'
	,ISNULL(hdr.CONTRACT_NUMBER_MODIFIER,'Null') AS 'Modifier'
	,hdr.COGNOMEN, 'National_Local' = 
		CASE hdr.COGNOMEN
			WHEN 'NATIONAL' THEN 'N'
			ELSE 'L'
			END

FROM dbo.SG_CRM_SA_HEADERS hdr
WHERE hdr.CONTRACT_NUMBER = '987965'

and no, I am not a student looking for homework help. Just new to this from MS access, where everything is easy!

sportsguy

MS Access 20 years, SQL hack

chadmat
The Chadinator

1974 Posts

Posted - 2012-10-29 : 16:19:17

DECLARE @i int

set @i = 1234

SELECT RIGHT('0000000000000000' + LTRIM(RTRIM(@i)),16)

-Chad

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-29 : 16:20:50

Are you sure you are on SQL 2012? I tested the following on my SQL 2012 and it works fine.

SELECT FORMAT(323,'00000');

select @@version will tell you the version you are using.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-29 : 16:39:01

quote:
Originally posted by sunitabeck

Are you sure you are on SQL 2012? I tested the following on my SQL 2012 and it works fine.
SELECT FORMAT(323,'00000');
select @@version will tell you the version you are using.

My guess is OP is just using SQL 2012 management studio and server that OP connects is one of earlier versions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sportsguy
Starting Member

39 Posts

Posted - 2012-10-30 : 13:43:10

Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Microsoft SQL Server Management Studio 11.0.2100.60
Running on Windows 7 (x64)

so the dba is going to look into why the function is not available

OP,
aka sportsguy

MS Access 20 years, SQL hack

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-30 : 13:45:45

quote:
Originally posted by sportsguy

Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Microsoft SQL Server Management Studio 11.0.2100.60
Running on Windows 7 (x64)

so the dba is going to look into why the function is not available

OP,
aka sportsguy

MS Access 20 years, SQL hack

]
check the compatibility level too

EXEC sp_dbcmptlevel 'your db name'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sportsguy
Starting Member

39 Posts

Posted - 2012-10-30 : 14:00:21

The current compatibility level is 110.

definately not sure what this means! LOL!
read my signature

thanks. . .

MS Access 20 years, SQL hack

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-30 : 14:19:37

As far as I know, this is not a T-SQL feature that can be turned on or off.

The only thing that comes to mind is if for some reason the CLR libraries are unavailable. FORMAT function is implemented as a CLR function and so it on .Net CLR being available. But that is a prerequisite even before you install SQL 2012. And, you don't have to enable CLR for the built-in functions that depend on CLR to work. So, that theory does not really fly.

Can you run the simplified query shown below just to humor us?

SELECT FORMAT(323,'00000');
SELECT FORMAT(323,'00000','en-US');

sportsguy
Starting Member

39 Posts

Posted - 2012-10-30 : 15:25:21

First off,

Thanks to chadmat as that query works as needed perfectly!

Second,

Sunitabeck, neither select statements, but I talked with the dba, he is aware of the new functions, and wants a specific example to research. I wil use your example, thank you

thank you everyone, I have many more quetions, but that is enough for today. . .

MS Access 20 years, SQL hack

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources