SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Truth table generator for any base!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 07/25/2007 :  08:21:55  Show Profile  Visit SwePeso's Homepage  Reply with Quote
This little handy algorithm provides a truth table for any range of numbers between 0 and 65535 of any base between 2 and 36!
As a twist, you can either return the digits concatenated or as separate columns.
CREATE PROCEDURE dbo.uspTruthTable
(
	@FromNumber SMALLINT = 0,
	@ToNumber INT = 255,
	@Step INT = 1,
	@Base TINYINT = 2,
	@Concat BIT = 0
)
AS

SET NOCOUNT ON

DECLARE @Temp INT,
	@SQL VARCHAR(8000),
	@b VARCHAR(12),
	@d VARCHAR(2),
	@Diff INT,
	@Digits CHAR(36)

IF @FromNumber > @ToNumber
	SELECT	@Temp = @FromNumber,
		@FromNumber = @ToNumber,
		@ToNumber = @Temp

IF @FromNumber < 0
	BEGIN
		RAISERROR('The parameter FromNumber %d is less than 0 (base 10).', 16, 1, @FromNumber)
		RETURN
	END

IF @ToNumber > 65535
	BEGIN
		RAISERROR('The parameter ToNumber %d is greater than 65535 (base 10).', 16, 1, @ToNumber)
		RETURN
	END

IF @Base < 2
	BEGIN
		RAISERROR('The parameter Base %d is less than 2 (base 10).', 16, 1, @Base)
		RETURN
	END

IF @Base > 36
	BEGIN
		RAISERROR('The parameter Base %d is greater than 36 (base 10).', 16, 1, @Base)
		RETURN
	END

SELECT	@SQL = '',
	@Temp = FLOOR(LOG(@ToNumber) / LOG(@Base)),
	@Diff = @ToNumber - @FromNumber + 1,
	@Digits = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'

WHILE @Temp >=0
	SELECT	@b = CONVERT(VARCHAR, @Base),
		@d = CONVERT(VARCHAR, @Temp),
		@SQL = @SQL +	CASE
					WHEN @Concat = 1 THEN '+SUBSTRING(''' + @Digits + ''',1 + Number/POWER(' + @b + ',' + @d + ') % ' + @b + ', 1)'
					ELSE ',SUBSTRING(''' + @Digits + ''',1 + Number/POWER(' + @b + ',' + @d + ') % ' + @b + ', 1) AS Digit' + @d
				END,
		@Temp = @Temp - 1

CREATE TABLE	#Numbers
		(
			Number INT PRIMARY KEY
		)

INSERT	#Numbers
	(
		Number
	)
VALUES	(
		@FromNumber
	)

WHILE @Step <= @Diff
	BEGIN
		INSERT	#Numbers
		SELECT	@Step + Number
		FROM	#Numbers
		WHERE	Number <= @ToNumber - @Step
		
		SET	@Step = @Step * 2
	END

IF @Concat = 1
	SET @SQL = 'SELECT Number, ''''' + @SQL + ' AS Digits FROM #Numbers ORDER BY Number'
ELSE
	SET @SQL = 'SELECT Number' + @SQL + ' FROM #Numbers ORDER BY Number'

EXEC (@SQL)

DROP TABLE #Numbers
You can use the algorithm like these examples
EXEC dbo.uspTruthTable
EXEC dbo.uspTruthTable 0, 255, 1, 8
EXEC dbo.uspTruthTable DEFAULT, 15, 1, 2


E 12°55'05.76"
N 56°04'39.42"

Edited by - SwePeso on 07/25/2007 08:36:37

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 07/25/2007 :  08:23:56  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Output from
EXEC dbo.uspTruthTable 0, 15, 1, 2
is
Number	Digit3	Digit2	Digit1	Digit0
------	------	------	------	------
 0	0	0	0	0
 1	0	0	0	1
 2	0	0	1	0 
 3	0	0	1	1
 4	0	1	0	0
 5	0	1	0	1
 6	0	1	1	0
 7	0	1	1	1
 8 	1	0	0	0
 9	1	0	0	1
10	1	0	1	0
11	1	0	1	1
12	1	1	0	0
13	1	1	0	1
14	1	1	1	0
15	1	1	1	1


E 12°55'05.76"
N 56°04'39.42"

Edited by - SwePeso on 07/25/2007 08:24:45
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 07/25/2007 :  08:37:54  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Output from
EXEC dbo.uspTruthTable 0, 255, 19, 16, 1
is
Number	Digits
------	------
 0	00
19	13
38	26
57	39
76	4C
95	5F
114	72
133	85
152	98
171	AB
190	BE
209	D1
228	E4
247	F7


E 12°55'05.76"
N 56°04'39.42"

Edited by - SwePeso on 07/25/2007 08:38:40
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000