Select Question - 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)

Select Question

Author

Topic

Technocrat
Starting Member

1 Post

Posted - 2003-11-07 : 15:09:26

Ok I have a database that has a field that is a string, but represents a number on a map. My problem is that for some reason that is a mystery, someone thought it would be a good idea to add a letter to a few of the numbers where a new map was added. Anyway when I go to pull the data, I have it sorted by those numbers so they are in order. But the few that have letters in them get pushed to the bottom. For example

050
053
065
090
202
50A
50C
53E

So my question is, is there a way I can fix this and get select to return the order "correctly":

050
50A
50C
053
53E
065
090
202

Without redoing the data?

Here is the code I have now:
SELECT * FROM HTEMODJ.UTM105AP ORDER BY UTCNL

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-11-07 : 15:29:42

You need to add a zero in front of the one's that are not alphanumeric.
Try this:


CREATE TABLE #Tmp (myField VARCHAR(20))

INSERT INTO #Tmp(myField) VALUES('050')
INSERT INTO #Tmp(myField) VALUES('053')
INSERT INTO #Tmp(myField) VALUES('065')
INSERT INTO #Tmp(myField) VALUES('090')
INSERT INTO #Tmp(myField) VALUES('202')
INSERT INTO #Tmp(myField) VALUES('50A')
INSERT INTO #Tmp(myField) VALUES('50C')
INSERT INTO #Tmp(myField) VALUES('53E')

SELECT *
FROM #Tmp 
ORDER BY 
	CASE 
		WHEN IsNumeric(myField) = 0 THEN '0' + myField 
		ELSE myField 
	END

DROP TABLE #Tmp

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

SamC
White Water Yakist

3467 Posts

Posted - 2003-11-07 : 16:25:06

Or, you could remove the trailing letter and sort. This might have an advantage if some field values have more than 3 characters?

The CASE can appear in the order by like in Michael's post or in the select list like this.

SELECT CASE WHEN IsNumeric(MyField) THEN MyField ELSE LEFT(MyField, LEN(MyField)-1) END AS MyNumericColumn
FROM MyTable
ORDER BY MyNumericColumn

This select gives numeric-only results, which may not be what you want...

Sam

SamC
White Water Yakist

3467 Posts

Posted - 2003-11-07 : 16:28:20

This may be faster since it checks only 1 character ??

SELECT CASE WHEN RIGHT(MyField,1) BETWEEN '0' AND '9' THEN MyField ELSE LEFT(MyField, LEN(MyField)-1) END AS MyNumericColumn
FROM MyTable
ORDER BY MyNumericColumn

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-07 : 17:06:26

HTEMODJ
UTM105AP
UTCNL

are those words in some foreign language ? if not, it must be awfully fun working with THAT database.

- Jeff

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-11-07 : 17:21:06

That's what I was thinking too. I can imagine some big cheat sheet of code look-up's beside this guy's desk.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources