?
Solved

SQL awkward behaviour (urgent)

Posted on 2009-12-22
10
Medium Priority
?
282 Views
Last Modified: 2013-11-16
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

0
Comment
Question by:vipin_nagarro
10 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 26110647
0
 
LVL 3

Author Comment

by:vipin_nagarro
ID: 26110685
@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
 
LVL 15

Expert Comment

by:rajeeshmca
ID: 26110692
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 26110727
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
 
LVL 3

Author Comment

by:vipin_nagarro
ID: 26110751
Im working on DB compatibility of 80, so i cant use row_number().
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 26110800
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
 
LVL 3

Author Comment

by:vipin_nagarro
ID: 26110944
@angell - Not working
(The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.)
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 300 total points
ID: 26111079
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
 
LVL 10

Accepted Solution

by:
Rakesh Jaimini earned 1200 total points
ID: 26111324
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
 
LVL 3

Author Closing Comment

by:vipin_nagarro
ID: 31669335
It Worked Thanks
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question