Adding the cubes of a strring of digits

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

Adding the cubes of a strring of digits

Author

Topic

davidc
Starting Member

26 Posts

Posted - 2007-05-09 : 06:51:33

I need to take a numeric value and add together the cubes of each single digit. The string length may vary from row to row.

As as example the number 1234 has individual cube values of 1 (1 cubed), 8 (2 cubed), 27 (3 cubed) and 64 (4 cubed). The resulting sum of the cubes is then 1+8+27+64 = 100. The same result would occur for 1243.

The purpose is to look for numeric values in a table where the same digits occur in two different rows but in a different order.

How can I create a function to apply to a varchar column to produce my integer result ?

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-09 : 08:29:37

Convert each character to ascii and take the cube as u mentioned

like Power(Ascii(substring(col1,1,1)),3)

one doubt, even sum also will give the correct value right.

1234 - 1+2+3+4 = 10
1324 - 1+3+2+4 = 10

why u want cube of each digit and sum???????

davidc
Starting Member

26 Posts

Posted - 2007-05-09 : 09:15:45

I am trying to create a function which can be applied to the numbers in a column. The data in this column contains invoice numbers input by hand by an input clerk. They sometimes get the numbers in the wrong order (e.g. 1243 instead of 1234). If I cube all the digits first then add them together I get a number which is the same for both rows. I can then look for invoices which have the same digits in a different order to check that they have not been input twice in error.
E.G the value is entered as 1234 the first time. Then a second person accidentally enters the same invoice but with an error in the number - 1243. The function will calculate the same sum of cube for these two entries.

The problem is the invoice number can have any number of digits so I need to start with digit one, and then digit two, then up to the last digit.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-09 : 09:18:25

What if there are actually two different invoices with their numbers differing in just a single place like 1234 and 1243 ?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

davidc
Starting Member

26 Posts

Posted - 2007-05-09 : 09:26:12

Not a problem - I only want to know if there are transposed invoice numbers. Other data in the table will confirm if they are transposed or not. I am more interested in finding someone who can help me with the function i need

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-09 : 09:43:05

you are better off with an algorithm that sorts the digits.

i.e., something that returns:

472912-->122479
742912-->122479
472921-->122479

this way you can verify for sure that if the formula returns the same value but the original entries do not match, a transposition has most likely occurred. Cubing the digits will not help you determine this.

What do you think?

- Jeff
http://weblogs.sqlteam.com/JeffS

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-09 : 09:54:32

Seems like you need a function to implement Hamming distance algorithm.

[url]http://en.wikipedia.org/wiki/Hamming_distance[/url]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-09 : 10:29:29

good call, harsh! that's exactly what he needs. and it is very simple to write!

- Jeff
http://weblogs.sqlteam.com/JeffS

X002548
Not Just a Number

15586 Posts

Posted - 2007-05-09 : 10:39:44

OK, what value does this have at all?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-09 : 10:41:30

And here's the UDF for you:

http://weblogs.sqlteam.com/jeffs/archive/2007/05/09/60197.aspx

Brett -- this is a way of determining if there are transposition errors between two values. his original idea for an algorithm wouldn't help at all, but the Hamming algorithm would do the trick.

- Jeff
http://weblogs.sqlteam.com/JeffS

X002548
Not Just a Number

15586 Posts

Posted - 2007-05-09 : 10:48:17

So how do you know if it's transposed or if it's they way it's suppose to be?

What kind of equality check is that?

It either is or it isn't

Now if you're talking about lookig for possible canidates for error, then I'll go with you there, but it still comes down to manually lookig at stuff, and still eliminates the full blown mistakes...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-09 : 10:48:36

Oh..Jeff, You beat me there!!

I was planning to write the function myself.

Why don't you move the function to the script library?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-09 : 13:02:22

Seems like a simplified method of Levenstiens distance algorithm.

This calculates the cube sum

declare @i int
set @i = 1234

select	sum(power(@i / power(10, d.number) % 10, 3)) as cubesum
from	(
		select	number
		from	master..spt_values
		where	number between 0 and 9
			and name is null
	) as d

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-10 : 06:12:17

Peso. You posted what I kept in mind
Alternatively you can squre the sum of digits also

select	square(sum(@i / power(10, d.number) % 10)) as cubesum
from	(
		select	number
		from	master..spt_values
		where	number between 0 and 9
			and name is null
	) as d

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources