Number of Character Permutations

Question

SQL Server 2008 R2

I want to find the number of possible permutations between two strings with 5 characters. Letters A-Z and numbers 0-9 are permitted. When counting, numbers are used before letters. For example 0,1,2,3,4,5,6,7,8,9,A,B,C,…

Example:
 ‘IZYZZ’ to ‘IZZZZ’
36 X 36 = 1296

Thank you,

Answer 1

Hello,

I wouldn't do this in SQL...

Your two strings can be considered as two numbers written in a base-36 arithmetic. Pretty much like hexadecimal uses 16 characters, your arithmetic uses 36 characters.

For each position, starting from the right and going to the left, measure the difference betwen the corresponding letter/number in each string. Then multiply this difference by 36 at the power of the column number -1. Finally, add all numbers

In your example :

IZZZZ and IZYZZ

First column is the rightmost one : Z - Z = 0. Multiply by 36 ^0. That is 0

Second column : Z - Z = 0. multiply by 36 ^1. That is 0

Third column is Z - Y = 1. Multiply by 36^2. That is 1296

etc

Answer 2

http://stackoverflow.com/questions/3621494/the-most-elegant-way-to-generate-permutations-in-sql-server

nice idea, you can prepare subset tables and then apply cross join :http://forums.teradata.com/forum/database/how-to-generate-permutations-using-sql

regards

joon

Answer 3

The links you gave tell how to find the mumber of posssible cominations for a single string based on the number of digits. I need to know the number of combinations between two strings.

Answer 4

The links you gave tell how to find the mumber of posssible cominations for a single string based on the number of digits. I need to know the number of combinations between two strings.

as sygrien has mentioned, you need to loop through OR write CTE for each character with the string count values, A=1, B=2, C=3,,,Z=26 etc.

maybe this link can give you little idea to start :http://code.openark.org/blog/mysql/checking-for-string-permutation

regards

joon