I want to be able to return only the SUM of those numbers in an address
field. What is the best way to do this?

Address Field:
1746 Wellesley
P.O. Box 3175
4141 65th Street
773 Florecita Terrace

Should return:
1746
3175
4206
773

I have googled this and found
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=51476

However I want to go a step further to return the sum of all numbers.

Currently I have a table with 50,000,000 rows. I am using an UDF but I find
it is very slow to create index on a calculated column based on the UDF. Also
I know the UDF can slow down query a lot.

Do you have any good idea?

Thanks,
James

just curious: why would you need that?

I am in a business that can receive the same person's address from multiple
sources. So the same customer's address may vary from one version to another.
For example, Street, st., str., one person's address1 in one list may be
address2 in another list, 10 broadway 201 may become 201 brd. way 10, etc.
But we have found that the sum of those numbers in address lines basically
doesn't change too much. In company with other information, we can uniquely
identify those persons, of course, only good enough to some extent.


Show quoteHide quote

Strange Timeout problem.
Book Recommendations
Question about functions
Implicit conversion from string to date
Speeding up queries using TOP