May 29, 2013 at 11:30 am
I need to take a number (an integer identity column) and convert it to a 4 character representation of that number, then back again. The limit that I have is that the integer has to be represented by a 4 character representation. I know that limits the maximum integer I can use but I should have some time before I reach that limit.
I've looked at converting to hex and some other things but this area really isn't my forte. This will initially be done on SQL Server 2005. Whatever routine I use, I'd like it to be relatively understandable by others. I could write a CLR function but it would be better if it were done using T-SQL as not everyone has a programming background.
Any suggestions?
May 29, 2013 at 11:59 am
Would help to see what you need done, not just a description. I know I am visual person when it comes to problem solving.
May 29, 2013 at 12:05 pm
This is a really bad idea. Just imagine all the 3 and 4 character swear words there are out there and that's just in the English language!
--Jeff Moden
Change is inevitable... Change for the better is not.
May 29, 2013 at 12:09 pm
Sure. I'm looking at base 36 right now.
The base 36 representation of decimal 1,679,615 (which I assume is the highest value I can use) would be ZZZZ. I could store that ZZZZ in my 4 character column, then convert it back to a maximum of 1,679,615.
But let's take a more reasonable number for an identity column. Let's take something like 250,000. The base 36 representation of that would be 5CWG. That would certainly fit in a 4 character column. Then, converting it back to decimal, I'd know I have to go to row 250,000 based on the integer identity column value.
Does that help? I'm just wondering if I'm going down the right path or if there might be a better way to handle this.
May 29, 2013 at 12:10 pm
Jeff, I work with what I'm given. And I've probably covered all the possible swear words just thinking of the best way to handle what I'm given.
May 29, 2013 at 12:26 pm
I don't understand why would you want to do that. However, I made a small excercise that might give you an idea.
WITH cteTally AS(SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) n
FROM sys.all_columns a,
sys.all_columns b),
cteCharExp AS(
SELECTCHAR( SUBSTRING( CAST( n AS varchar( 8)), 1, 2) + 33) +
CASE WHEN LEN( n) > 2 THEN CHAR( SUBSTRING( CAST( n AS varchar( 10)), 3, 2) + 33)ELSE '' END +
CASE WHEN LEN( n) > 4 THEN CHAR( SUBSTRING( CAST( n AS varchar( 10)), 5, 2) + 33)ELSE '' END +
CASE WHEN LEN( n) > 6 THEN CHAR( SUBSTRING( CAST( n AS varchar( 10)), 7, 2) + 33)ELSE '' END Expression,
n
FROM cteTally)
SELECTExpression,
CAST( ASCII( SUBSTRING( Expression, 1, 1)) - 33 AS VARCHAR(2)) +
CASE WHEN LEN( Expression) > 1 THEN CAST( ASCII( SUBSTRING( Expression , 2, 1)) - 33 AS VARCHAR(2)) ELSE '' END +
CASE WHEN LEN( Expression) > 2 THEN CAST( ASCII( SUBSTRING( Expression , 3, 1)) - 33 AS VARCHAR(2)) ELSE '' END +
CASE WHEN LEN( Expression) > 3 THEN CAST( ASCII( SUBSTRING( Expression , 4, 1)) - 33 AS VARCHAR(2)) ELSE '' END,
n
FROM cteCharExp
May 29, 2013 at 12:52 pm
here;s an example, with 0000 thru ZZZZ as the min/max values:
the first just generates example,s the second makes a calculated column based on the identity value of a table:
SELECT POWER(36,4) -1 As MaxChar,
MiniTally.n,
CASE
WHEN (MiniTally.n /POWER(36,3)) % 36 <= 9
THEN CHAR((MiniTally.n /POWER(36,3)) % 36 + 48)
ELSE CHAR((MiniTally.n /POWER(36,3)) % 36 + 55)
END
+ CASE
WHEN (MiniTally.n /POWER(36,2)) % 36 <= 9
THEN CHAR((MiniTally.n /POWER(36,2)) % 36 + 48)
ELSE CHAR((MiniTally.n /POWER(36,2)) % 36 + 55)
END
+ CASE
WHEN (MiniTally.n /36) % 36 <= 9
THEN CHAR((MiniTally.n /36) % 36 + 48)
ELSE CHAR((MiniTally.n /36) % 36 + 55)
END
--last character, 0-9AZ
+ CASE
WHEN MiniTally.n % 36 <=9
THEN CHAR(MiniTally.n % 36 + 48)
ELSE CHAR(MiniTally.n % 36 + 55)
END AS [ 0-9AZ]
FROM (SELECT 0 As n UNION ALL
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
FROM sys.columns c1
CROSS JOIN sys.columns c2) MiniTally
WHERE MiniTally.n < POWER(36,4)
CREATE TABLE X(XID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CHECK ((XID>= 0) AND (XID < POWER(36,4))), --limit=26 * 26 + 999 + 1
XCALCULATED AS
CASE
WHEN (XID /POWER(36,3)) % 36 <= 9
THEN CHAR((XID /POWER(36,3)) % 36 + 48)
ELSE CHAR((XID /POWER(36,3)) % 36 + 55)
END
+ CASE
WHEN (XID /POWER(36,2)) % 36 <= 9
THEN CHAR((XID /POWER(36,2)) % 36 + 48)
ELSE CHAR((XID /POWER(36,2)) % 36 + 55)
END
+ CASE
WHEN (XID /36) % 36 <= 9
THEN CHAR((XID /36) % 36 + 48)
ELSE CHAR((XID /36) % 36 + 55)
END
--last character, 0-9AZ
+ CASE
WHEN XID % 36 <=9
THEN CHAR(XID % 36 + 48)
ELSE CHAR(XID % 36 + 55)
END,
SOMEOTHERCOL VARCHAR(30) )
INSERT INTO X(SOMEOTHERCOL) VALUES('WHATEVER')
SET IDENTITY_INSERT X ON
INSERT INTO X(XID,SOMEOTHERCOL) VALUES(675999,'MORESTUFF')
SET IDENTITY_INSERT X OFF
SET IDENTITY_INSERT X ON
INSERT INTO X(XID,SOMEOTHERCOL) VALUES(676000,'MORESTUFF')
INSERT INTO X(XID,SOMEOTHERCOL) VALUES(POWER(36,4) -1,'MORESTUFF')
SET IDENTITY_INSERT X OFF
SELECT * FROM X
Lowell
May 29, 2013 at 12:55 pm
Just curious. Why is there a need for this?
May 29, 2013 at 1:11 pm
Okay, the need for all this is:
A process inserts a parent row, then takes the identity value and puts it as the first 4 characters as part of a longer string in many other child rows pointing to the parent. Is there a better way to handle this parent/child relationship? Absolutely! But at the moment, that would require a lot of logic changes in a lot of different areas. Like I said, sometimes you have to work with what you're given.
So where does that leave me? The maximum identity value I can store in the alphanumeric string is '9999.' Not the greatest planning, right? But it is what it is for now. So now I need to fit 10000 (the next highest identity) into those same four characters. The only way I can think to do that is not use base 10 for those characters but use something else that allows me to store that longer identity value. It is, after all, a string value, so I'm not limited to integers.
Is this a long term solution? I certainly hope not!
May 29, 2013 at 1:15 pm
Sorry, above response posted twice due by mistake.
May 30, 2013 at 6:48 am
rburko (5/29/2013)
Jeff, I work with what I'm given. And I've probably covered all the possible swear words just thinking of the best way to handle what I'm given.
Heh... that's the worst excuse for doing something wrong in the world. Advise the people that gave you these requirements and see if they don't agree that the swear word problem is a problem. If they don't, THEN you're technically off the hook although if something comes of it, they will still blame you.
You say you've "covered all the possible swear words"... does that mean you've done something to avoid them showing up in the database?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 30, 2013 at 6:54 am
No, I haven't done anything to avoid them being used in the database. I've just said most of them dealing with this issue.
May 30, 2013 at 6:58 am
rburko (5/29/2013)
Okay, the need for all this is:A process inserts a parent row, then takes the identity value and puts it as the first 4 characters as part of a longer string in many other child rows pointing to the parent. Is there a better way to handle this parent/child relationship? Absolutely! But at the moment, that would require a lot of logic changes in a lot of different areas. Like I said, sometimes you have to work with what you're given.
So where does that leave me? The maximum identity value I can store in the alphanumeric string is '9999.' Not the greatest planning, right? But it is what it is for now. So now I need to fit 10000 (the next highest identity) into those same four characters. The only way I can think to do that is not use base 10 for those characters but use something else that allows me to store that longer identity value. It is, after all, a string value, so I'm not limited to integers.
Is this a long term solution? I certainly hope not!
Base 36 will only take you out to 1.6 million and Base 62 (0-9,A-Z,a-z) will only take you out to 14 million although it will need to be a case sensitive column. Is that going to be enough?
You can also avoid the absolute spelling of English swear words simply by removing vowels. It will cut Base 36 down to Base 31 and the number of values down to 932 thousand. And since it appears to be your idea to use Base 36, I'd suggest you avoid the swear words or managment will get you.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 30, 2013 at 7:06 am
Good advice, Jeff. Thanks.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply