Converting integer to ascii - 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

SQL Server Development (2000)

Converting integer to ascii

Author

Topic

deserel
Starting Member

4 Posts

Posted - 2004-08-16 : 17:46:22

I have a query that I am concatonating together fields and I want to order by that concatonation.

So, I'll have
1:1
1:2
2:1
2:2
etc

This works great, until you get to 10

1:1
1:2
10:1
2:1
etc

My thought is to convert these to letters. So, 1 = A, 2 = B etc.

I tried, CHAR(ASCII(DisplayOrder)+16)) which seems to be the right formula, but it converts 1 to A and 2 to B (great!!) but it also converts 11 to A and 22 to B.

Display Order in this case is an Int.

It seems to only look at the first number.

What am I doing wrong?

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-08-16 : 19:07:11

Can you give a list of sample data and your expected query output? I can't see what you're trying to do.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-16 : 19:14:22

I don't understand what you're trying to do either. :) If you order by the len of the first column, then order by the ASCII and number combination you will get the right order as described in your example.
MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-08-16 : 19:22:57

In your query you use CHAR which means only 1 character. You need to parse the field.
If there is really always a : and only 1 of them you can use parsename

select displayorder
from mytable
order by cast ( parsename (replace(displayorder,':','.'),2) as int )

--Ken
"Knowledge is a process of piling up facts; wisdom lies in their simplification."

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-08-16 : 20:19:17

the ordering has nothing to do with the concatenation.
if the two columns you are concatenating are ints, you just do this:

select convert(A, varchar(10)) + ':' + convert(B, varchar(10)) as NewExpr
from yourtable
order by A,B

if they are text, you do the opposite:

select A + ':' + B as NewExpr
from yourtable
order by convert(A, int),convert(B,int)

just because you are concatenting two columns together doesn't mean you can't ORDER BY a different expression. (as long as you are not summarizing or selecting DISTINCT rows, that is)

- Jeff

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-08-17 : 02:00:15

How about putting leading zeros on the front of the numbers.

eg RIGHT('0000000000' + LTRIM(RTRIM(STR(NumberA, 10))), 10) + ':' + RIGHT('0000000000' + LTRIM(RTRIM(STR(NumberB, 10))), 10)

Then you can order by this too.
:)

Duane.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-08-17 : 08:44:52

quote:

How about putting leading zeros on the front of the numbers.

eg RIGHT('0000000000' + LTRIM(RTRIM(STR(NumberA, 10))), 10) + ':' + RIGHT('0000000000' + LTRIM(RTRIM(STR(NumberB, 10))), 10)

Then you can order by this too.
:)

i wouldn't do that .. why work to acheive the effct of ordering by a,b using conversions and concatentation and padding zeroes and forfeiting any use of possible indexes or optimizations, when you can just say ORDER BY A,B ???

- Jeff

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-08-17 : 08:58:12

Makes Perfect sense.
:)

Duane.

deserel
Starting Member

4 Posts

Posted - 2004-08-17 : 09:31:22

OK, some good suggestions, but I'm not sure they will work in my case. :)

I have a heirarchy tree that I am displaying. For example

Sports (1)
---Team Sports (1)
------Swimming (1)
------Baseball (2)
---Individual Sports (2)
------Swimming (1)

(- are just for spacing)

'Sports' is the 1 one displayed under level 1
'Team Sports' is 1 under 'Sports'
'Individual Sports' is 2 under 'Sports'
'Swimming' is 1 under 'Team Sports'
'Baseball' is 2 under 'Team Sports'

I thought I would create a display by column that looks like this for the above

1
1:1
1:1:1
1:1:2
1:2
1:2:1

then order by that column, which works great until you get above 9.

I can't do some of the suggestions as this is a product of 3 Union All statements with the order on the entire statement.

I spent several hours trying to do this displaying of the tree in a nice way and ended up powering through it grabing Level 1, then Level 2, then Level 3, each in their own select statement then Unioning them together.

If you are really curious about the select statement.

I have 2 tables.

bhb_marketing_category_tree
TreeID
ParentID --Parent Category (One parent can have multiple children)
CategoryID -- Child Category (one child can have multiple parents)
DisplayOrder

bhb_marketing_categories
MarketingCategoryID
CategoryName

select tree.TreeID, cat.CategoryName,
CHAR(ASCII(tree.DisplayOrder)+16)as DisplayOrder
from bhb_marketing_category_tree tree,
bhb_marketing_categories cat
where tree.CategoryID = cat.MarketingCategoryID
and tree.ParentID is NULL
UNION ALL
select tree.TreeID, cat2.CategoryName+':'+cat.CategoryName AS CategoryName, char(ASCII(tree2.DisplayOrder)+16)+':'+char(ASCII(tree.DisplayOrder)+16) AS DisplayOrder
from bhb_marketing_category_tree tree,
bhb_marketing_category_tree tree2,
bhb_marketing_categories cat,
bhb_marketing_categories cat2
where tree.CategoryID = cat.MarketingCategoryID
and cat2.MarketingCategoryID = tree.ParentID
and tree2.CategoryID=tree.ParentID
and tree2.ParentID is Null
UNION ALL
select tree.TreeID, cat3.CategoryName+':'+cat2.CategoryName+':'+cat.CategoryName AS CategoryName, char(ASCII(tree3.DisplayOrder)+16)+':'+char(ASCII(tree2.DisplayOrder)+16)+':'+char(ASCII(tree.DisplayOrder)+16) AS DisplayOrder
from bhb_marketing_category_tree tree,
bhb_marketing_category_tree tree2,
bhb_marketing_category_tree tree3,
bhb_marketing_categories cat,
bhb_marketing_categories cat2,
bhb_marketing_categories cat3
where tree.CategoryID = cat.MarketingCategoryID
and cat2.MarketingCategoryID = tree.ParentID
and cat3.MarketingCategoryID = tree2.ParentID
and tree2.CategoryID=tree.ParentID
and tree3.CategoryID=tree2.ParentID
and tree3.ParentID is Null
order by DisplayOrder

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-17 : 10:26:55

this becomes very easy if you use 0 prefix before a single digit number:

declare @MyTable table
(
col1 varchar(50)
)

insert into @MyTable(col1)
select '01' union all
select '01:01' union all
select '01:01:01' union all
select '01:02' union all
select '01:09' union all
select '01:10' union all
select '01:12' union all
select '01:03' union all
select '01:04' union all
select '01:06' union all
select '01:02' union all
select '01:02:01' union all
select '01:02:09' union all
select '01:02:10' union all
select '01:02:11'

select * from @MyTable
order by col1

Go with the flow & have fun! Else fight the flow :)

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-08-17 : 10:35:42

Yeah, but as the cross join doctor said - You can't make use of the indexes etc when doing an order by if you do this.

Duane.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-17 : 10:45:44

that is true, but as deserel said, he wants to make a display by column, so i think this is the easiest way.
it would store the data in a varchar(50 or more) and it can be indexed.
so the row value would be 01:01:10 and not 1:1:10

Go with the flow & have fun! Else fight the flow :)

deserel
Starting Member

4 Posts

Posted - 2004-08-17 : 11:18:00

Adding the 0 in front of the single digits did the trick!

Thanks!

I am not so concerned about indexing... this table will never be huge and we'll use it to build nightly web pages, so it it runs slower, it won't hurt anything.

Now.. if anyone wanted to help me print off the tree recursively instead of by brute force. :)

I am actually printing these off as

Sports
Sports:IndividualSports
Sports:IndividualSports:Baseball

Which will be used in drop down menus.

Thanks for your help!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources