SQL awkward behaviour (urgent)

Hi  All

Below I have attached the code where im getting some problem.

This will first create a temp table named #t1 with one column c1 with data as follows:
3A
1B
2C

I need to get comma seperated string with order of numeral part (excluding last char), so I cast it like cast(SUBSTRING(c1, 0, len(c1)) as int) in order by clause.

Please run the attached query.
Now the result Im getting is a bit awkward.

@var returns '3A,'   but I was expecting '1B,2C,3A'.

Could some body tell me why such a behaviour.
Also let me know the alternate approach for this.
Please help.
 
Thanks
Vipin Goel
create table #t1 (c1 varchar(5))

insert into #t1 values ('3A')
insert into #t1 values ('1B')
insert into #t1 values ('2C')

declare @var varchar(100)
set @var = ''

select  @var = @var + c1 + ',' 
from #t1
order by cast(SUBSTRING(c1, 0, len(c1)) as int) 

select * from #t1

select cast(c1 as varchar)
from #t1
order by cast(SUBSTRING(c1, 0, len(c1)) as int) 

select @var

drop table #t1

Open in new window

LVL 3
vipin_nagarroAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
vipin_nagarroAuthor Commented:
@angelll - this is a completely new approach, the code i have given is already implemented. So it will be difficult to turn to this.
Also I am more interested in the reason for such a behaviour.
 
0
 
rajeeshmcaCommented:
Hi,

try the following

create function [dbo].[fn_Ttest]
      RETURNS VARCHAR(200)
      AS
      BEGIN
      DECLARE @Result VARCHAR(200)
      SELECT @Result = stuff(
                        (    
                              select
                              ', ' + c1
                              from
                                    #t1
                        FOR XML PATH('')), 1, 2, '')
      RETURN @Result
      END
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I tested this:
select  @var = coalesce(@var + ',', '') + c1 
from ( select c1 , row_number() over (order by cast(SUBSTRING(c1, 0, len(c1)) as int)) rn
          from #t1 
     ) sq  
order by rn

Open in new window

0
 
vipin_nagarroAuthor Commented:
Im working on DB compatibility of 80, so i cant use row_number().
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ok, this worked also:
select  @var = coalesce(@var + ',', '') + c1  
from ( select c1 
         from #t1  
        order by cast(SUBSTRING(c1, 0, len(c1)) as int)
     ) sq   
order by rn

Open in new window

0
 
vipin_nagarroAuthor Commented:
@angell - Not working
(The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
fix
select  @var = coalesce(@var + ',', '') + c1   
from ( select top 100 percent c1  
         from #t1   
        order by cast(SUBSTRING(c1, 0, len(c1)) as int) 
     ) sq    
order by rn

Open in new window

0
 
Rakesh JaiminiCommented:
Hi,

please check this
select  @var = @var + c1 + ',' 
from #t1
group by c1, cast(SUBSTRING(c1, 1, len(c1)-1 ) as int) 
order by cast(SUBSTRING(c1, 1, len(c1)-1 ) as int)

Open in new window

0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
vipin_nagarroAuthor Commented:
It Worked Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.