Sq Ft to Acres - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Sq Ft to Acres

Author

Topic

jscot
Posting Yak Master

106 Posts

Posted - 2011-03-22 : 20:58:46

How I can convert sq ft to acres
Below are sample data
1776 sq ft
1.848
1.510
0.217
323 sq. ft.
0.733
0.350 AC
0.0377
92 Sq. Ft.
Note:- I have data like this I want to convert only if the data has sq ft. I want to know sql syntax to convert this one. Thank you.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-03-22 : 22:27:06

[code]-- generate sample data --
Declare @t table (c1 varchar(16))

INSERT @t VALUES('1776 sq ft')
INSERT @t VALUES('1.848')
INSERT @t VALUES('1.510')
INSERT @t VALUES('0.217')
INSERT @t VALUES('323 sq. ft.')
INSERT @t VALUES('0.733')
INSERT @t VALUES('0.350 AC')
INSERT @t VALUES('0.0377')
INSERT @t VALUES('92 Sq. Ft.')

-- return only those with sq ft in it. --
SELECT CONVERT(decimal(38, 19),
LTRIM(RTRIM(
SUBSTRING(c1, 1, CHARINDEX('sq', c1)-1)
))
) * (2.29568411) / 100000
AS acres

FROM @t
WHERE c1 LIKE '%sq%ft%'[/code]

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-03-22 : 22:34:59

Are you just asking for the formula to convert square feet to acres?

1 square mile = 5280 ft*5280 ft = 27,878,400 sq ft = 640 acres

1 acre = 27,878,400 sq ft/640 acres = 43,560 sq ft/acre

select Acres = convert(numeric(20,10),1775) / convert(numeric(20,10),43560)

Result:

Acres
--------------------
0.040748393021120293

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources