Ordinating records... - 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)

Ordinating records...

Author

Topic

masterx81
Starting Member

12 Posts

Posted - 2003-09-05 : 10:30:39

Hi, I need to order some records by a varchar field...
The standerd order in case of varchar is:
'1 blabla'
'11 blabla'
'2 blabla'
'22 bla bla'
Etc...

I need to order the records by the first number, without changing the structure of the database:

'1 blabla'
'2 blabla'
'11 blabla'
'22 bla bla'

Thanks!!!

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-09-05 : 10:35:10

quote:
I need to order the records by the first number

Come again? Your example doesn't do that. If it did it would look like:

'1 blabla'
'11 blabla'
'2 blabla'
'22 bla bla'

or

'11 blabla'
'1 blabla'
'22 bla bla'
'2 blabla'

or

'11 blabla'
'1 blabla'
'2 blabla'
'22 bla bla'

or

'1 blabla'
'11 blabla'
'22 bla bla'
'2 blabla'

Jonathan
{0}

masterx81
Starting Member

12 Posts

Posted - 2003-09-05 : 10:41:57

Sorry :-P
I'm italian and i have difficulty to write correct in italian, so, in english... :P

The standard ORDER BY (varchar column) statemet order the records:
1 1 asti
2 2 asti
3 26 asti
4 27 Asti
5 3 asti

I need the order in the follow mode:
1 1 asti
2 2 asti
5 3 asti
3 26 asti
4 27 Asti

I need to order not by the first number, sorry, but to order by the number that is fisrt of the 'asti'...
Thenks!

rharmon
Starting Member

41 Posts

Posted - 2003-09-05 : 10:52:48

It's a little crude, but are you looking for something like this?

create table #blablah
(blahfield varchar(50))

insert into #blablah (blahfield) values( '1 blabla')
insert into #blablah (blahfield) values( '11 blabla')
insert into #blablah (blahfield) values( '2 blabla')
insert into #blablah (blahfield) values( '22 bla bla')

select *
from #blablah
order by
-- get the integer before the space
convert(int,left(blahfield,charindex(' ',blahfield)))
-- get the text after the space
, right(blahfield,len(blahfield)-charindex(' ',blahfield))

masterx81
Starting Member

12 Posts

Posted - 2003-09-05 : 10:56:21

the problem is that some records have the number and the text joined:

12Asti

or other that have only text

Asti

Is possible???
:'(

mr_mist
Grunnio

1870 Posts

Posted - 2003-09-05 : 11:10:09

You need to sort out your records. Split them into two fields. For the time being, try something like..

select *
from #blablah
order by
case when charindex(' ',blahfield) > 0 then
-- get the integer before the space
convert(int,left(blahfield,charindex(' ',blahfield)))
-- get the text after the space

else
1
end, right(blahfield,len(blahfield)-charindex(' ',blahfield))

But that wont really help you. Your problem is with the data, not with the SELECT. And if any of you text has spaces then even the above query will fall over.

GIGO I'm afraid.

-------
Moo. :)

masterx81
Starting Member

12 Posts

Posted - 2003-09-05 : 11:17:43

Is possible to make a loop in sql???
I loop until the end of the varchar, and test it with the isnumeric function...
It's only in my dream?

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-09-05 : 11:40:02

OK, Page47 is in a bad mood today.

<rant>
First, stop and go read this http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=28942

Second, post the DDL of your table and DML to insert sample data that covers all your particular cases and finally a valid expected rowset, based on that sample data, with the ordering you are looking for. Please stop changing the requirements and the senerio every time someone makes a suggestion. It is frustrating for the people trying to help you (for free).

Look at all these people taking shots in the dark to help you. Look at all the wasted time and effort simply because you are not giving all the relevent information. I can appreciate a language barrier as much as the next guy, but as far as I can tell we communicate in english here, so you either need to learn to (or put more effort into) communicate(ing) or go ask someone that understands you.
</rant>

I'm not trying to flame. I am tyring to help both the poster and the SQL Team community. My tone is harsh, but it reflects my feelings at the moment.

Jay White
{0}

rharmon
Starting Member

41 Posts

Posted - 2003-09-05 : 11:50:58

This is getting uglier all the time, I'm in concurrance with MR Mist, bad architecture leads to bad days.

If you create a udf to handle the numeric placement stuff, you can force it to work.

create table blablah
(blahfield varchar(50))

insert into blablah (blahfield) values( '1 blabla')
insert into blablah (blahfield) values( '11 blabla')
insert into blablah (blahfield) values( '2 blabla')
insert into blablah (blahfield) values( '22 bla bla')
insert into blablah (blahfield) values( '22sla bla')
insert into blablah (blahfield) values( 'sla bla')
insert into blablah (blahfield) values( 's1la bla')
insert into blablah (blahfield) values( 'isla bla')

go

CREATE FUNCTION get_leading_numbers
(@instring varchar(50))
RETURNS int
AS
BEGIN

declare @string varchar(50)
declare @counter int
declare @charholder char(1)
set @charholder = '0'
set @counter = 1
set @string = @instring

while isnumeric(@charholder) = 1
begin
set @charholder = substring(@string,@counter,1)
set @counter = @counter + 1

end
RETURN @counter - 2

END

go

select blahfield from blablah
order by left(blahfield, dbo.get_leading_numbers(blahfield)),
ltrim(right(blahfield,len(blahfield) - dbo.get_leading_numbers(blahfield)))

masterx81
Starting Member

12 Posts

Posted - 2003-09-05 : 11:58:43

Thanks to all.
I think to use the method of rharmon.
Monday I return to work and I work on this example. THANKS!!!

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-05 : 12:16:24

quote:
Originally posted by Page47

OK, Page47 is in a bad mood today.

<rant>
First, stop and go read this http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=28942

Second, post the DDL of your table and DML to insert sample data that covers all your particular cases and finally a valid expected rowset, based on that sample data, with the ordering you are looking for. Please stop changing the requirements and the senerio every time someone makes a suggestion. It is frustrating for the people trying to help you (for free).

Look at all these people taking shots in the dark to help you. Look at all the wasted time and effort simply because you are not giving all the relevent information. I can appreciate a language barrier as much as the next guy, but as far as I can tell we communicate in english here, so you either need to learn to (or put more effort into) communicate(ing) or go ask someone that understands you.
</rant>

I'm not trying to flame. I am tyring to help both the poster and the SQL Team community. My tone is harsh, but it reflects my feelings at the moment.

Jay White
{0}

Wow Jay...stay away from the dark side...

HEY...I just noticed..it's FRIDAY!!!

da dada da da da da..TEQULIA!

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

masterx81
Starting Member

12 Posts

Posted - 2003-09-09 : 07:14:47

In the case that other people needs this triks... :

The function:
CREATE FUNCTION get_number (@instring varchar(50)) RETURNS int AS
BEGIN
declare @counter int
declare @charholder char(1)
set @charholder = '0'
set @counter = 1
while isnumeric(@charholder) = 1
begin
set @charholder = substring(@instring,@counter,1)
set @counter = @counter + 1
End
IF ((@counter-1) > 0)
BEGIN
set @counter = cast((substring(@instring,1,@counter -2)) as int)
End
else set @counter = 0
RETURN @counter
End

The Call:
select * from lala order by dbo.get_number(Field),field

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources