Interesting Task - 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)

Interesting Task

Author

Topic

erwine
Yak Posting Veteran

69 Posts

Posted - 2006-05-19 : 02:43:21

Hi everybody..

Let's say i have @parameter = '0001'
Im thinking of writing a function to increment the parameter.
This is how it should work:
@parameter + 8 = '0009'
@parameter + 9 = '000A'
@parameter + 11 = '000B'
@parameter + 34 = '000Z'
@parameter + 35 = '0010'

'001A' + 20 = '001U'

something like this? anyone has ideas??

... sql is fun...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-19 : 02:49:27

What do you want to achieve ?

Do you need to pass unknown number of parameters into the Stored Procedure ?

erwine
Yak Posting Veteran

69 Posts

Posted - 2006-05-19 : 02:57:55

basically i will have :
declare @parameter varchar(4)

then i wanna do:
select A.*, @@identity, Function1(@@identity + @parameter)

... sql is fun...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-05-19 : 03:20:07

It seems that you need a BASE 36 calculator. Also there is an error in your supplied example code!
@parameter + 11 = '000B' is wrong! Should be @parameter + 11 = '000C'

Anyway, here is the function to achieve the result you are looking for:
--------------------------------------------------------
Call function with

SELECT dbo.fnBase36('001A', 20)
--------------------------------------------------------
CREATE FUNCTION dbo.fnBase36
(
@OldNumber VARCHAR(4),
@NewValue INT
)
RETURNS VARCHAR(4)
AS

BEGIN
IF @OldNumber IS NULL OR @OldNumber = '' OR LEN(@OldNumber) > 4
RETURN NULL

DECLARE @Base36 VARCHAR(36),
@Base INT,
@Index INT,
@CharValue INT,
@Sum INT

SELECT @Base36 = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
@Index = LEN(@OldNumber),
@Base = 1,
@Sum = 0

WHILE @Index > 1
BEGIN
SELECT @CharValue = CHARINDEX(SUBSTRING(@OldNumber, @Index, 1), @Base36) - 1
IF @CharValue < 0
RETURN NULL

SELECT @Sum = @Sum + @Base * @CharValue,
@Base = @Base * 36,
@Index = @Index - 1
END

IF @Sum + @NewValue > 1679615
RETURN NULL

SELECT @OldNumber = '',
@Base = 46656,
@Sum = @Sum + @NewValue

WHILE @Base > 0
BEGIN
SELECT @Index = @Sum / @Base

SELECT @OldNumber = @OldNumber + SUBSTRING(@Base36, @Index + 1, 1),
@Sum = @Sum - @Index * @Base,
@Base = @Base / 36
END

RETURN RIGHT('0000' + @OldNumber, 4)
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-05-19 : 03:23:17

quote:
Originally posted by khtan

Still not sure why you want to do this.

The only way is using Dynamic SQL

KH

No, you don't have to resort to dynamic SQL. Some basic math is required, that's all. It's called Base Converting.

Erwine, use this code for your result!
------------------------------------------------
declare @parameter varchar(4)

then i wanna do:
select A.*, @@identity, dbo.fnBase36(@parameter, @@identity)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-19 : 03:26:00

quote:
Originally posted by Peso

quote:
Originally posted by khtan

Still not sure why you want to do this.

The only way is using Dynamic SQL

KH

No, you don't have to resort to dynamic SQL. Some basic math is required, that's all. It's called Base Converting.

Erwine, use this code for your result!
------------------------------------------------
declare @parameter varchar(4)

then i wanna do:
select A.*, @@identity, dbo.fnBase36(@parameter, @@identity)

Miss interpret the requirement

erwine
Yak Posting Veteran

69 Posts

Posted - 2006-05-19 : 03:31:30

Thx PESO..it's nice.
it works perfectly for 4digits..
however it has problem with arithmetic overflows when i try with 10digits.

... sql is fun...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-05-19 : 03:38:03

You mean like this?

ALTER FUNCTION dbo.fnBase36
(
@OldNumber VARCHAR(10),
@NewValue BIGINT
)
RETURNS VARCHAR(10)
AS

BEGIN
IF @OldNumber IS NULL OR LEN(@OldNumber) < 1 OR LEN(@OldNumber) > 4
RETURN NULL

DECLARE @Base36 VARCHAR(36),
@Base BIGINT,
@Index TINYINT,
@CharValue TINYINT,
@Sum BIGINT

SELECT @Base36 = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
@Index = LEN(@OldNumber),
@Base = 1,
@Sum = 0

WHILE @Index > 1
BEGIN
SELECT @CharValue = CHARINDEX(SUBSTRING(@OldNumber, @Index, 1), @Base36) - 1
IF @CharValue < 0
RETURN NULL

SELECT @Sum = @Sum + @Base * @CharValue,
@Base = @Base * 36,
@Index = @Index - 1
END

IF @Sum + @NewValue >= CONVERT(BIGINT, POWER(36.0, 10.0))
RETURN NULL

SELECT @OldNumber = '',
@Base = CONVERT(BIGINT, POWER(36.0, 9.0)),
@Sum = @Sum + @NewValue

WHILE @Base > 0
BEGIN
SELECT @Index = @Sum / @Base

SELECT @OldNumber = @OldNumber + SUBSTRING(@Base36, @Index + 1, 1),
@Sum = @Sum - @Index * @Base,
@Base = @Base / 36
END

RETURN RIGHT('0000000000' + @OldNumber, 10)
END

erwine
Yak Posting Veteran

69 Posts

Posted - 2006-05-19 : 03:40:08

oo icic..
so it BIGINT..get it..thx so much peso.
thanks to khtan as well for some advices. :p

cheers,
erwine

... sql is fun...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-05-19 : 03:40:42

Sorry, missed a "> 4". Should by "> 10"...

ALTER FUNCTION dbo.fnBase36
(
@OldNumber VARCHAR(10),
@NewValue BIGINT
)
RETURNS VARCHAR(10)
AS

BEGIN
IF @OldNumber IS NULL OR LEN(@OldNumber) < 1 OR LEN(@OldNumber) > 10
RETURN NULL

DECLARE @Base36 VARCHAR(36),
@Base BIGINT,
@Index TINYINT,
@CharValue TINYINT,
@Sum BIGINT

SELECT @Base36 = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
@Index = LEN(@OldNumber),
@Base = 1,
@Sum = 0

WHILE @Index > 1
BEGIN
SELECT @CharValue = CHARINDEX(SUBSTRING(@OldNumber, @Index, 1), @Base36) - 1
IF @CharValue < 0
RETURN NULL

SELECT @Sum = @Sum + @Base * @CharValue,
@Base = @Base * 36,
@Index = @Index - 1
END

IF @Sum + @NewValue >= CONVERT(BIGINT, POWER(36.0, 10.0))
RETURN NULL

SELECT @OldNumber = '',
@Base = CONVERT(BIGINT, POWER(36.0, 9.0)),
@Sum = @Sum + @NewValue

WHILE @Base > 0
BEGIN
SELECT @Index = @Sum / @Base

SELECT @OldNumber = @OldNumber + SUBSTRING(@Base36, @Index + 1, 1),
@Sum = @Sum - @Index * @Base,
@Base = @Base / 36
END

RETURN RIGHT('0000000000' + @OldNumber, 10)
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-05-19 : 03:49:01

Final version! Missed a bug when not having a leading zero for parameter...

CREATE FUNCTION dbo.fnBase36
(
@OldNumber VARCHAR(10),
@NewValue BIGINT
)
RETURNS VARCHAR(10)
AS

BEGIN
IF @OldNumber IS NULL OR LEN(@OldNumber) < 1 OR LEN(@OldNumber) > 10
RETURN NULL

DECLARE @Base36 VARCHAR(36),
@Base BIGINT,
@Index TINYINT,
@CharValue TINYINT,
@Sum BIGINT

SELECT @Base36 = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
@Index = LEN(@OldNumber),
@Base = 1,
@Sum = 0

WHILE @Index > 0
BEGIN
SELECT @CharValue = CHARINDEX(SUBSTRING(@OldNumber, @Index, 1), @Base36) - 1
IF @CharValue < 0
RETURN NULL

SELECT @Sum = @Sum + @Base * @CharValue,
@Base = @Base * 36,
@Index = @Index - 1
END

IF @Sum + @NewValue >= CONVERT(BIGINT, POWER(36.0, 10.0))
RETURN NULL

SELECT @OldNumber = '',
@Base = CONVERT(BIGINT, POWER(36.0, 9.0)),
@Sum = @Sum + @NewValue

WHILE @Base > 0
BEGIN
SELECT @Index = @Sum / @Base

SELECT @OldNumber = @OldNumber + SUBSTRING(@Base36, @Index + 1, 1),
@Sum = @Sum - @Index * @Base,
@Base = @Base / 36
END

RETURN RIGHT('0000000000' + @OldNumber, 10)
END

erwine
Yak Posting Veteran

69 Posts

Posted - 2006-05-19 : 03:50:52

ALTER FUNCTION dbo.fnBase36
(
@Old VARCHAR(8),
@New BIGINT
)
RETURNS VARCHAR(8)
AS

BEGIN
IF @Old IS NULL OR LEN(@Old) < 1 OR LEN(@Old) > 8
RETURN NULL

DECLARE @Base36 VARCHAR(36),
@Base BIGINT,
@Index TINYINT,
@CharValue TINYINT,
@Sum BIGINT

SELECT @Base36 = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
@Index = LEN(@Old),
@Base = 1,
@Sum = 0

WHILE @Index > 1
BEGIN
SELECT @CharValue = CHARINDEX(SUBSTRING(@Old, @Index, 1), @Base36) - 1
IF @CharValue < 0
RETURN NULL

SELECT @Sum = @Sum + @Base * @CharValue,
@Base = @Base * 36,
@Index = @Index - 1
END

IF @Sum + @New >= CONVERT(BIGINT, POWER(36.0, 8.0))
RETURN NULL

SELECT @Old = '',
@Base = CONVERT(BIGINT, POWER(36.0, 7.0)),
@Sum = @Sum + @New

WHILE @Base > 0
BEGIN
SELECT @Index = @Sum / @Base

SELECT @Old = @Old + SUBSTRING(@Base36, @Index + 1, 1),
@Sum = @Sum - @Index * @Base,
@Base = @Base / 36
END

RETURN RIGHT('00000000' + @Old, 8)
END

this code's for 8digits, which is the one that i want to do.
however it doesnt give correct answer for
select dbo.fnBase36('921001A1', 100) as test

is it because BIGINT is not big enough??

... sql is fun...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-05-19 : 03:59:13

No, I get '921001CT' as result with my final version of dbo.fnBase36

Just change "WHILE @Index > 1" to "WHILE @Index > 0" !

erwine
Yak Posting Veteran

69 Posts

Posted - 2006-05-19 : 04:00:47

ya..i just finished changing it to >=1 hahahaha
thx thx..

... sql is fun...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-05-19 : 04:01:31

CREATE FUNCTION dbo.fnBase36
(
@OldNumber VARCHAR(8),
@NewValue BIGINT
)
RETURNS VARCHAR(8)
AS

BEGIN
IF @OldNumber IS NULL OR LEN(@OldNumber) < 1 OR LEN(@OldNumber) > 8
RETURN NULL

DECLARE @Base36 VARCHAR(36),
@Base BIGINT,
@Index TINYINT,
@CharValue TINYINT,
@Sum BIGINT

SELECT @Base36 = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
@Index = LEN(@OldNumber),
@Base = 1,
@Sum = 0

WHILE @Index > 0
BEGIN
SELECT @CharValue = CHARINDEX(SUBSTRING(@OldNumber, @Index, 1), @Base36) - 1
IF @CharValue < 0
RETURN NULL

SELECT @Sum = @Sum + @Base * @CharValue,
@Base = @Base * 36,
@Index = @Index - 1
END

IF @Sum + @NewValue >= CONVERT(BIGINT, POWER(36.0, 8))
RETURN NULL

SELECT @OldNumber = '',
@Base = CONVERT(BIGINT, POWER(36.0, 7)),
@Sum = @Sum + @NewValue

WHILE @Base > 0
BEGIN
SELECT @Index = @Sum / @Base

SELECT @OldNumber = @OldNumber + SUBSTRING(@Base36, @Index + 1, 1),
@Sum = @Sum - @Index * @Base,
@Base = @Base / 36
END

RETURN RIGHT('00000000' + @OldNumber, 8)
END

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-19 : 08:13:03

Hi all,

You can create generalised version (i.e. for base N

) with the functions below. Here are a few example calls:

SELECT dbo.fnAddBaseN('921001A1', 100, 36) as 'Equals 921001CT'
SELECT dbo.fnAddBaseN('12345', 100, 10) as 'Equals 12445'
SELECT dbo.fnAddBaseN('101', 100, 2) as 'Equals 1101001'

The first 2 functions were taken from this very (very) useful resource:
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=502&lngWId=5

CREATE function DECTON(@n bigint, @b int)
--Converts a decimal number to base n.
returns varchar(255)
as
BEGIN
DECLARE @i bigint,@temp int, @s varchar(255)
SET @i=@n
SET @s=''
WHILE (@i>0)
BEGIN
    SET @temp=@i % @b
    SET @i=@i /@b
    IF @temp>9
    	SET @s=char(55+@temp)+@s
    ELSE
    	SET @s=char(48+@temp)+@s
END
RETURN @s
END
go

CREATE function NTODEC(@s VARCHAR(255), @b bigint) 
--Converts a number on base n to decimal.
returns bigint
as
BEGIN
DECLARE @i int, @temp char(1), @result bigint
SELECT @i=1
SELECT @result=0
WHILE (@i<=LEN(@s))
BEGIN
    SELECT @temp=UPPER(SUBSTRING(@s,@i,1))
    IF (@temp>='0') AND (@temp<='9') 
    	SELECT @result=@result+ (ASCII(@temp)-48)*POWER(@b,LEN(@s)-@i)
    ELSE
    	SELECT @result=@result+ (ASCII(@temp)-55)*POWER(@b,LEN(@s)-@i)
    SELECT @i=@i+1
END
return @result
END
go

CREATE FUNCTION dbo.fnAddBaseN(@BaseNValue VARCHAR(10), @DecValue BIGINT, @Base INT)
RETURNS VARCHAR(10) AS
BEGIN
    RETURN dbo.DECTON(dbo.NTODEC(@BaseNValue, @Base) + @DecValue, @Base)
END
go

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources