string sorting - 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 2005 Forums

Transact-SQL (2005)

string sorting

Author

Topic

dani2
Starting Member

32 Posts

Posted - 2007-11-19 : 04:01:43

Hi
How ca I sort this:
1.|1.1.|10.|2.|2.1
to this
1.|1.1.|2.|2.1|10.

thank you

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-19 : 04:06:11

you can't without some heavy string manipulation.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-19 : 09:11:49

Is this a string or rows of value ? or column of values ?

KH
[spoiler]Time is always against us[/spoiler]

dani2
Starting Member

32 Posts

Posted - 2007-11-19 : 09:12:04

quote:
Originally posted by spirit1

you can't without some heavy string manipulation.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

so be it :(
Have an idea how it can be done in SQL?

dani2
Starting Member

32 Posts

Posted - 2007-11-19 : 09:14:41

quote:
Originally posted by khtan

Is this a string or rows of value ? or column of values ?

KH
[spoiler]Time is always against us[/spoiler]

This is a column is the table:
[Code]
1.
1.1.
10.
2.
2.1.

must be sorted like this:
[Code]
1.
1.1.
2.
2.1.
10.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-19 : 09:27:40

make use of http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033 to extract each segment and convert to integer for sorting (ordering)

KH
[spoiler]Time is always against us[/spoiler]

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-19 : 09:29:27

order by '0000' + [code]

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-19 : 09:35:47

quote:
Originally posted by spirit1

order by '0000' + [code]

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

that wouldn't work right ?

KH
[spoiler]Time is always against us[/spoiler]

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-19 : 09:38:27

why not?
since data is in a column as i understand.

yeah i see it...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-19 : 09:44:13

but '0000' + '10.' will still be less than '0000' + '2.'

KH
[spoiler]Time is always against us[/spoiler]

dani2
Starting Member

32 Posts

Posted - 2007-11-19 : 09:45:43

So its possible only using a function to parse the string?
It cannot be done in one complicated select statement right?

hitman
Starting Member

23 Posts

Posted - 2007-11-19 : 09:51:09

Yes, probably you should use a function or so.

-----------------------
http://www.sqltips.info
-----------------------

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-11-19 : 09:51:49

YOu gave some sample data, but you need to clearly and completely define your specifications. Can there be more than 2 parts? Can there be anything other than numbers? can it ever start with a period or have "missing parts" like 1..3 ? And so on.

- Jeff
http://weblogs.sqlteam.com/JeffS

hitman
Starting Member

23 Posts

Posted - 2007-11-19 : 09:53:32

check this

create table #tmp (part float)

declare @string varchar(1000)
set @string = '1.|1.1|10.|2.|2.1|'

while charindex('|', @string) <> 0
begin
insert into #tmp values (cast(left(@string, charindex('|', @string) - 1) AS float))
set @string = right(@string, len(@string) - charindex('|', @string))
end

select * from #tmp order by part
drop table #tmp

you probably would have to modify string - change 1.1. to 1.1

-----------------------
http://www.sqltips.info
-----------------------

dani2
Starting Member

32 Posts

Posted - 2007-11-19 : 09:57:50

quote:
Originally posted by jsmith8858

YOu gave some sample data, but you need to clearly and completely define your specifications. Can there be more than 2 parts? Can there be anything other than numbers? can it ever start with a period or have "missing parts" like 1..3 ? And so on.

- Jeff
http://weblogs.sqlteam.com/JeffS

Sorry for that.
The values can only contain numbers separated by "." and cannot have missing parts.
Exactly like a table of contents can be something like 1.4. but also something like 1.4.1.1.

dani2
Starting Member

32 Posts

Posted - 2007-11-19 : 10:01:00

quote:
Originally posted by hitman

check this

create table #tmp (part float)

declare @string varchar(1000)
set @string = '1.|1.1|10.|2.|2.1|'

while charindex('|', @string) <> 0
begin
insert into #tmp values (cast(left(@string, charindex('|', @string) - 1) AS float))
set @string = right(@string, len(@string) - charindex('|', @string))
end

select * from #tmp order by part
drop table #tmp

you probably would have to modify string - change 1.1. to 1.1

-----------------------
http://www.sqltips.info
-----------------------

The type of the column is Varchar. Sorry I forgot to specify that.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-11-19 : 11:26:54

Is there a maximum number of segments delimited by periods? i.e., can it go on and on like 2.2.4.4.355.230.01 or it is limited to 2 parts, as shown in your sample data? And does it always end in a period?

- Jeff
http://weblogs.sqlteam.com/JeffS

dani2
Starting Member

32 Posts

Posted - 2007-11-19 : 12:38:22

quote:
Originally posted by jsmith8858

Is there a maximum number of segments delimited by periods? i.e., can it go on and on like 2.2.4.4.355.230.01 or it is limited to 2 parts, as shown in your sample data? And does it always end in a period?

- Jeff
http://weblogs.sqlteam.com/JeffS

Ok lets say maximum of 3 numbers and always separated by period and ends in a period

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-19 : 13:28:59

[code]order by parsename(Code + ' ', 2), parsename(Code + ' ', 3), parsename(Code + ' ', 4)[/code]

KH
[spoiler]Time is always against us[/spoiler]

X002548
Not Just a Number

15586 Posts

Posted - 2007-11-19 : 13:39:24

hold on, you've posted it 2 ways..as many rows, and as 1 row delimited by a pipe

which is it?



CREATE TABLE myTable99(Col1 varchar(20))
GO

INSERT INTO myTable99(Col1)
SELECT '1.' 	UNION ALL
SELECT '1.1.' 	UNION ALL
SELECT '10.' 	UNION ALL
SELECT '2.' 	UNION ALL
SELECT '2.1.'
GO

SELECT Col1
FROM myTable99
ORDER BY CONVERT(decimal(15,4),REVERSE(SUBSTRING(REVERSE(Col1),2,LEN(Col1)-1)))
GO

DROP TABLE myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2007-11-19 : 13:42:04

quote:
Originally posted by dani2

quote:
Originally posted by jsmith8858

Is there a maximum number of segments delimited by periods? i.e., can it go on and on like 2.2.4.4.355.230.01 or it is limited to 2 parts, as shown in your sample data? And does it always end in a period?

- Jeff
http://weblogs.sqlteam.com/JeffS

[quote]
Ok lets say maximum of 3 numbers and always separated by period and ends in a period

It would be ALOT easier if you actually posted sample code like I did above

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources