order by just letters - 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

Transact-SQL (2000)

order by just letters

Author

Topic

the1gadget
Yak Posting Veteran

55 Posts

Posted - 2006-04-04 : 11:18:41

Hi All

I need to sort some data just by the letters in a column so that the following lines.

+30 Houses (Outline)
.86 Houses
15 Flats
\Offices/Houses
Military Barracks
Office (Refurbishment)
Supermarket (Extension)
University (Extension)

Come out like

15 Flats
.86 Houses
+30 Houses (Outline)
Military Barracks
Office (Refurbishment)
\Offices/Houses
Supermarket (Extension)
University (Extension)

i.e.
what it has sorted on is

Flats
Houses
HousesOutline
Military Barracks
OfficeRefurbishment
OfficesHouses
SupermarketExtension
UniversityExtension

Any one got any pointers ?

--
David

Kristen
Test

22859 Posts

Posted - 2006-04-04 : 11:38:31

So ... if I've got this right ... you want to sort by the column with all the NON-AlphaNumeric characters removed?

(I would propose that you leave SPACE in their too, and possibly convert all punctuation to SPACE - i.e. "XXXYYY" sorts after "XXX YYY" and "XXX/YYY")

Kristen

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-04-04 : 11:49:27

Use this function


CREATE FUNCTION dbo.GetCharacters(@myString varchar(500), @validChars varchar(100))
RETURNS varchar(500) AS
BEGIN
 
	While @myString like '%[^' + @validChars + ']%'
		Select @myString = replace(@myString,substring(@myString,patindex('%[^' + @validChars + ']%',@myString),1),'')

	Return @myString
END

then you can sort by letters


Select someField From <yourTable> Where SomeConditionIsTrue Order By dbo.getCharacters(someField,'A-Za-z')

or with kristens suggestion


Select someField From <yourTable> Where SomeConditionIsTrue Order By dbo.getCharacters(someField,'A-Za-z ')

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

the1gadget
Yak Posting Veteran

55 Posts

Posted - 2006-04-04 : 11:50:46

Unfortunatly not what is required.

I think that I an going to make a new colunm with just the info in that I want to sort by.

--
David

Kristen
Test

22859 Posts

Posted - 2006-04-04 : 11:53:02

"Unfortunatly not what is required"

Can you define what the requirement is then pls, and then we can tell you the solution!

Kristen

the1gadget
Yak Posting Veteran

55 Posts

Posted - 2006-04-04 : 11:54:20

Previously Reply was to Kristen
Thanks Corey that looks like the solution.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-05 : 01:58:25

You can learn more from this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56713

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources