Order a Number - 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 2000 Forums

Transact-SQL (2000)

Order a Number

Author

Topic

Friznost
Starting Member

20 Posts

Posted - 2009-02-13 : 12:56:37

How do I order a number.

Ex:
21354 --> 12345
21354 --> 54321

OR

58967 --> 56789
58967 --> 98765

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-13 : 13:01:21

SELECT Col1,
CASE WHEN Col1 LIKE '%1%' THEN '1' ELSE '' END
+CASE WHEN Col1 LIKE '%2%' THEN '2' ELSE '' END
+CASE WHEN Col1 LIKE '%3%' THEN '3' ELSE '' END
+CASE WHEN Col1 LIKE '%4%' THEN '4' ELSE '' END
+CASE WHEN Col1 LIKE '%5%' THEN '5' ELSE '' END
+CASE WHEN Col1 LIKE '%6%' THEN '6' ELSE '' END
+CASE WHEN Col1 LIKE '%7%' THEN '7' ELSE '' END
+CASE WHEN Col1 LIKE '%8%' THEN '8' ELSE '' END
+CASE WHEN Col1 LIKE '%9%' THEN '9' ELSE '' END AS Peso
FROM Table1

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-13 : 13:04:42

And if there's a chance for duplicate digits

SELECT	Col1,
	REPLICATE('1', DATALENGTH(Col1) - DATALENGTH(REPLACE(Col1, '1', '')))
	+ REPLICATE('2', DATALENGTH(Col1) - DATALENGTH(REPLACE(Col1, '2', '')))
	+ REPLICATE('3', DATALENGTH(Col1) - DATALENGTH(REPLACE(Col1, '3', '')))
	+ REPLICATE('4', DATALENGTH(Col1) - DATALENGTH(REPLACE(Col1, '4', '')))
	+ REPLICATE('5', DATALENGTH(Col1) - DATALENGTH(REPLACE(Col1, '5', '')))
	+ REPLICATE('6', DATALENGTH(Col1) - DATALENGTH(REPLACE(Col1, '6', '')))
	+ REPLICATE('7', DATALENGTH(Col1) - DATALENGTH(REPLACE(Col1, '7', '')))
	+ REPLICATE('8', DATALENGTH(Col1) - DATALENGTH(REPLACE(Col1, '8', '')))
	+ REPLICATE('9', DATALENGTH(Col1) - DATALENGTH(REPLACE(Col1, '9', ''))) AS Peso
FROM 	Table1

E 12°55'05.63"
N 56°04'39.26"

Friznost
Starting Member

20 Posts

Posted - 2009-02-13 : 13:16:58

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-13 : 13:20:09

quote:
Originally posted by Friznost

How do I order a number.

Ex:
21354 --> 12345
21354 --> 54321

OR

58967 --> 56789
58967 --> 98765

make a UDF like this


CREATE FUNCTION GetOrdered
(
@Number int,
@Direction int
)
RETURNS int
AS
DECLARE @Temp TABLE
(
Value int
)
BEGIN
DECLARE @Result varchar(1000),@L int,@C char(1),@Var varchar(1000)
SET @Var=@Number
SET @L=LEN(@Number)
WHILE @L>0
BEGIN
SELECT @C=LEFT(@Var,1),@Var=SUBSTRING(@Var,2,LEN(@Var)-1)
INSERT @Temp VALUES (@C*1)
SET @L=LEN(@Var)
END
SELECT @Result=COALESCE(@Result,'') + CAST(Value AS char(1))
FROM @Temp
ORDER BY CASE WHEN @Direction=1 THEN Value 
         ELSE -1 * Value
         END

RETURN @Result*1
END

call it like

SELECTf field,dbo.GetOrdered(field,1) AS conv1,dbo.GetOrdered(field,2) AS conv2 FROM Yourtable

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-13 : 13:39:53

Number may not be integer. It can be a string containing answers to a quiz.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-13 : 13:44:54

Or make the function INLINE, for speed?

CREATE FUNCTION dbo.fnGetOrder
(
	@Ascending BIT = 1,
	@Data VARCHAR(200)
)
RETURNS VARCHAR(200)
AS
BEGIN
	RETURN	CASE @Ascending
			WHEN 0 THEN
				REPLICATE('9', DATALENGTH(@Data) - DATALENGTH(REPLACE(@Data, '9', '')))
				+ REPLICATE('8', DATALENGTH(@Data) - DATALENGTH(REPLACE(@Data, '8', '')))
				+ REPLICATE('7', DATALENGTH(@Data) - DATALENGTH(REPLACE(@Data, '7', '')))
				+ REPLICATE('6', DATALENGTH(@Data) - DATALENGTH(REPLACE(@Data, '6', '')))
				+ REPLICATE('5', DATALENGTH(@Data) - DATALENGTH(REPLACE(@Data, '5', '')))
				+ REPLICATE('4', DATALENGTH(@Data) - DATALENGTH(REPLACE(@Data, '4', '')))
				+ REPLICATE('3', DATALENGTH(@Data) - DATALENGTH(REPLACE(@Data, '3', '')))
				+ REPLICATE('2', DATALENGTH(@Data) - DATALENGTH(REPLACE(@Data, '2', '')))
				+ REPLICATE('1', DATALENGTH(@Data) - DATALENGTH(REPLACE(@Data, '1', '')))
			ELSE	
				REPLICATE('1', DATALENGTH(@Data) - DATALENGTH(REPLACE(@Data, '1', '')))
				+ REPLICATE('2', DATALENGTH(@Data) - DATALENGTH(REPLACE(@Data, '2', '')))
				+ REPLICATE('3', DATALENGTH(@Data) - DATALENGTH(REPLACE(@Data, '3', '')))
				+ REPLICATE('4', DATALENGTH(@Data) - DATALENGTH(REPLACE(@Data, '4', '')))
				+ REPLICATE('5', DATALENGTH(@Data) - DATALENGTH(REPLACE(@Data, '5', '')))
				+ REPLICATE('6', DATALENGTH(@Data) - DATALENGTH(REPLACE(@Data, '6', '')))
				+ REPLICATE('7', DATALENGTH(@Data) - DATALENGTH(REPLACE(@Data, '7', '')))
				+ REPLICATE('8', DATALENGTH(@Data) - DATALENGTH(REPLACE(@Data, '8', '')))
				+ REPLICATE('9', DATALENGTH(@Data) - DATALENGTH(REPLACE(@Data, '9', '')))
		END
END

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources