August 3, 2012 at 9:59 am
Peter Brinkhaus (8/3/2012)
sestell1 (8/3/2012)
Interesting thread.Well, in that case, here's another one. Only works on INT's >= 0.
SELECT
N,
LEFT(CAST(N % 10 AS CHAR(1)) +
CAST((N / 10) % 10 AS CHAR(1)) +
CAST((N / 100) % 10 AS CHAR(1)) +
CAST((N / 1000) % 10 AS CHAR(1)) +
CAST((N / 10000) % 10 AS CHAR(1)) +
CAST((N / 100000) % 10 AS CHAR(1)) +
CAST((N / 1000000) % 10 AS CHAR(1)) +
CAST((N / 10000000) % 10 AS CHAR(1)) +
CAST((N / 100000000) % 10 AS CHAR(1)) +
CAST((N / 1000000000) % 10 AS CHAR(1)), LEN(CAST(N AS VARCHAR(10))))
FROM
(
VALUES (521), (67342), (14), (4), (2111222333), (0), (4000)
) SampleData(N)
If you want it to work for N<0
SELECT
N,
LEFT(CAST(ABS(N % 10) AS CHAR(1)) +
CAST(ABS((N / 10) % 10) AS CHAR(1)) +
CAST(ABS((N / 100) % 10) AS CHAR(1)) +
CAST(ABS((N / 1000) % 10) AS CHAR(1)) +
CAST(ABS((N / 10000) % 10) AS CHAR(1)) +
CAST(ABS((N / 100000) % 10) AS CHAR(1)) +
CAST(ABS((N / 1000000) % 10) AS CHAR(1)) +
CAST(ABS((N / 10000000) % 10) AS CHAR(1)) +
CAST(ABS((N / 100000000) % 10) AS CHAR(1)) +
CAST(ABS((N / 1000000000) % 10) AS CHAR(1)),
CASE WHEN N < 0 THEN LEN(CAST(N AS VARCHAR(10))) - 1 ELSE LEN(CAST(N AS VARCHAR(10))) END) +
CASE WHEN N < 0 THEN '-' ELSE '' END
FROM
(
VALUES (-521), (-67342), (-14), (4), (2111222333), (0), (4000)
) SampleData(N)
August 3, 2012 at 10:32 am
Yet another in a cast of many:
DECLARE @num INT
set @num= 102948092
DECLARE @numstr VARCHAR(100)
SET @numstr = @num
DECLARE @revstr VARCHAR(100)
set @revstr=replicate(' ',len(@numstr));
;WITH
t1 AS (SELECT 1 N UNION ALL SELECT 1 N),
t2 AS (SELECT 1 N FROM t1 x, t1 y),
t3 AS (SELECT 1 N FROM t2 x, t2 y),
t4 AS (SELECT 1 N FROM t3 x, t3 y),
t5 AS (SELECT 1 N FROM t4 x, t4 y),
Tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N
FROM t3 x, t3 y)
select @revstr=stuff(@revstr,len(@numstr)-n+1,1,substring(@numstr,n,1)) from tally where n<=len(@numstr)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 3, 2012 at 3:24 pm
Here is one with a rCTE. to put it back together you can use FOR XML PATH or SELECT @Var = @Var + part. i have done one with FOR XML PATH so figured id post one with SELECT @Var = @Var + part
DECLARE @num BIGINT
set @num= 102995348548092
DECLARE @numstr VARCHAR(100)
SET @numstr = @num
DECLARE @STR VARCHAR(100) = ''
;WITH rCTE(ID,Ch) AS (SELECT 1, SUBSTRING(@numstr,1,1)
UNION ALL
SELECT ID + 1, SUBSTRING(@numstr,ID+1,1)
FROM rCTE
WHERE ID < DATALENGTH(@numstr))
SELECT @STR = @STR + Ch FROM rCTE ORDER BY ID DESC
SELECT @STR
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
August 4, 2012 at 3:11 am
How about this Cadavre???.....
Declare @num bigint
Set @num = 12345678
Select @num As Number, Stuff((select Left(Right(Cast(@num As Varchar(10)), n), 1) From Tally
Where n <= Len(Cast(@num As Varchar(10))) For XML Path('')), 1, 1, Right(Cast(@num As Varchar(10)), 1)) As ReverseOfNumber
😛
September 10, 2012 at 1:22 am
Better late than never?
DECLARE @num BIGINT = 1234567890
;WITH Tally (n) AS (
SELECT TOP (LEN(@num)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns)
SELECT (SELECT SUBSTRING(RTRIM(@Num), 1+LEN(@Num)-n, 1) FROM Tally FOR XML PATH(''))
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 10, 2012 at 4:53 am
DECLARE @number INT = 1000;
SELECT STUFF(
(
SELECT SUBSTRING(CAST(@number AS VARCHAR(19)),Num.N,1)
FROM sys.columns Col
INNER JOIN
(
SELECT N
FROM
( VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9), (10),(11),(12),(13),(14),(15),(16),(17), (18),(19)
)A (N)
)Num on Num.N<=(LEN(@number))
Group By Num.N
ORDER BY Num.N DESC
FOR XML PATH (''),Type
).value('.', 'VARCHAR(19)')
,1,0,'')
September 10, 2012 at 12:28 pm
For such questions, I ask the person asking the question to write the word or number to be reversed on a piece of paper. Then I flip it over, hold it up to a bright light, and say "See? That's how!" 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
September 10, 2012 at 9:19 pm
Jeff Moden (9/10/2012)
For such questions, I ask the person asking the question to write the word or number to be reversed on a piece of paper. Then I flip it over, hold it up to a bright light, and say "See? That's how!" 😛
If anybody asked you that question in an interview, they'd deserve that answer. 😛
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 15, 2013 at 3:21 am
Here is my method.
I created a UDF.
CREATE FUNCTION dbo.ReverseNumber ( @iNumber int )
RETURNS INT
AS
BEGIN
DECLARE @len int
DECLARE @rev int
DECLARE @tens int
IF @iNumber < 10
SET @rev = @iNumber
ELSE
BEGIN
SET @len=LEN(convert(varchar,@iNumber))-1
SET @tens = 1
SET @rev = 0
WHILE @len >0
BEGIN
SET @tens = @tens * 10
SET @len = @len-1
END
SET @rev = (@iNumber % 10) * @tens + dbo.ReverseNumber(@iNumber/10)
END
RETURN (@rev)
END
Now Try the function
SELECT dbo.ReverseNumber(1234)
Output: 4321
January 15, 2013 at 3:39 am
If we're reviving old, dead threads then I say there's always this:
DECLARE @num BIGINT = 1234567890
,@numstr VARCHAR(20) = ''
;WITH Tally (n) AS (
SELECT n=number
FROM [master].dbo.spt_values Tally
WHERE [Type] = 'P' AND Number BETWEEN 1 AND LEN(@num))
SELECT @numstr = @numstr + SUBSTRING(CAST(@num AS VARCHAR(20)), 1+LEN(@num)-n, 1)
FROM Tally
SELECT @numstr
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply