troubleshooting Question

SQL awkward behaviour (urgent)

Avatar of vipin_nagarro
vipin_nagarroFlag for India asked on
DatabasesMicrosoft SQL Server 2005SQL
10 Comments2 Solutions302 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Rakesh Jaimini

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 10 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros