0

It not really a problem but i am curious to know why. I am using sql server and using FORMAT in a number returns a varchar with 4000 lenght. Why?

select format(1,'00')

This query returns "01"

VVT
  • 72
  • 1
  • 7
  • Fair question. "The length of the return value is determined by the format." https://learn.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql?view=sql-server-ver15#return-types – shawnt00 Aug 24 '21 at 18:09
  • Of course if you change that input value to 999 you'll see that the output overflows the format string too. – shawnt00 Aug 24 '21 at 18:17
  • It's .NET's String.Format, documented here: https://learn.microsoft.com/en-us/dotnet/standard/base-types/custom-numeric-format-strings#the-0-custom-specifier – David Browne - Microsoft Aug 24 '21 at 18:48
  • 1
    The actual length is 2 characters, 4000 is just the length of the data type – Charlieface Aug 24 '21 at 19:31

1 Answers1

2

Consider a query like

drop table if exists #t

create table #t(val int, fmt varchar(20))

insert into #t(val,fmt) values (1,'00')
insert into #t(val,fmt) values (1,'0000000')
insert into #t(val,fmt) values (1,'0000000000000000000')

select format(val,fmt) formatted from #t

What data type should formatted be? It can't change per row, so a single type must be chosen to hold all the values. And nvarchar(4000) is a reasonable choice, as it can hold any string with up to 4000 characters.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67