Link to home
Start Free TrialLog in
Avatar of vipin_nagarro
vipin_nagarroFlag for India

asked on

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

Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Avatar of vipin_nagarro

ASKER

@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.
 
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
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

Im working on DB compatibility of 80, so i cant use row_number().
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

@angell - Not working
(The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.)
SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
It Worked Thanks