Checking for transposed numbers

[Scott Levine]

Does anyone have a UDF or Stored Procedure that checks for transposed
numbers in a group?

[David Portas]

Scott Levine from Atlanta?

Here's an example that might help, depending on your exact definition of
"transposed". This query finds rows with exactly the same digits in any
order.

CREATE TABLE SomeTable (x INTEGER NOT NULL)
/* Sample data */
INSERT INTO SomeTable (x) VALUES (3412)
INSERT INTO SomeTable (x) VALUES (4567)
INSERT INTO SomeTable (x) VALUES (4321)

SELECT T1.x
FROM SomeTable AS T1
JOIN SomeTable AS T2
ON
REPLICATE('0',LEN(T1.x)-LEN(REPLACE(T1.x,'0','')))
+REPLICATE('1',LEN(T1.x)-LEN(REPLACE(T1.x,'1','')))
+REPLICATE('2',LEN(T1.x)-LEN(REPLACE(T1.x,'2','')))
+REPLICATE('3',LEN(T1.x)-LEN(REPLACE(T1.x,'3','')))
+REPLICATE('4',LEN(T1.x)-LEN(REPLACE(T1.x,'4','')))
+REPLICATE('5',LEN(T1.x)-LEN(REPLACE(T1.x,'5','')))
+REPLICATE('6',LEN(T1.x)-LEN(REPLACE(T1.x,'6','')))
+REPLICATE('7',LEN(T1.x)-LEN(REPLACE(T1.x,'7','')))
+REPLICATE('8',LEN(T1.x)-LEN(REPLACE(T1.x,'8','')))
+REPLICATE('9',LEN(T1.x)-LEN(REPLACE(T1.x,'9','')))
=
REPLICATE('0',LEN(T2.x)-LEN(REPLACE(T2.x,'0','')))
+REPLICATE('1',LEN(T2.x)-LEN(REPLACE(T2.x,'1','')))
+REPLICATE('2',LEN(T2.x)-LEN(REPLACE(T2.x,'2','')))
+REPLICATE('3',LEN(T2.x)-LEN(REPLACE(T2.x,'3','')))
+REPLICATE('4',LEN(T2.x)-LEN(REPLACE(T2.x,'4','')))
+REPLICATE('5',LEN(T2.x)-LEN(REPLACE(T2.x,'5','')))
+REPLICATE('6',LEN(T2.x)-LEN(REPLACE(T2.x,'6','')))
+REPLICATE('7',LEN(T2.x)-LEN(REPLACE(T2.x,'7','')))
+REPLICATE('8',LEN(T2.x)-LEN(REPLACE(T2.x,'8','')))
+REPLICATE('9',LEN(T2.x)-LEN(REPLACE(T2.x,'9','')))
AND T1.x<>T2.x
--
David Portas
SQL Server MVP
--

[--CELKO--]

>> Does anyone have a UDF or Stored Procedure that checks for
transposed numbers in a group? <<

Can we get a better spec and sample data? Numbers are not transposed;
They are abstractions which do not have an ordering. Letters and
Numerals can be transposed. There are pairwise and disjoint
transposes; do you care what kind of transpose? Is there a maximum
length?

Short strings can be done with a table look up, which will be in
parallel as a JOIN and much faster than a proprietary UDF.