SQL Question

[Technocrat]

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. So the 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

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

[wey97]

I think you are out of luck.

The entries with letters don't get pushed to the bottom because they have letters.

It happens because of the intial characters:
0..
0..
0..
0..
2..
5..
5..
5..

You could have three fields - one numeric and one character or string and concatenate them to the third field.

50
50 + A
50 + C
53
53 + E
65
90
202

order by the numeric first and then by the character field.

[Technocrat]

Yeah I know. But you know how some times you HOPE that there is an answer if though you are pretty sure there isnt one? Thats what I am doing

I wish that person hadnt done that with the numbers

[techgnome]

Actually, there might be a way.... it's just a matter of getting each of the recrords into the same numeric + alpha format....
Let me chew on this for a few min and see what I can come up with.....
In the meantime, are all records int eh field the same length? or does it vary as well?


TG

[Technocrat]

Any help would be great thanks.

Yes they are all 3 positions

[techgnome]

That rocks!
Here's your solution

SELECT *
FROM tblTest
Order By 
	CASE
		WHEN ISNUMERIC(RIGHT(TestFld,1)) <> 0 THEN
			TestFld + ' '
		ELSE
			'0' + TestFld
	END

I created a table, loaded the values you gave, ran this and got everything in the order you expected.... it's strangly simple...

By testing the last character of the string to see if it's numeric, we can manipulate it to sort differently.
If the last char is numeric, we add a space to the end of it. If it is not numeric, we add a 0 to the front.... so internally, we end up with the folowing:

TestFld        
------- ------ 
050     050_
50A     050A
50C     050C
053     053_
53E     053E
065     065_
090     090_
202     202_

The inderscores are there to represent the spaces that were added
since a space has less value than a letter or a number, 050 floats to the top, followed by A and C, then by 53 and so on....

TG

[Mongo]

Or if you've a need or interest in learning about efficiency, deterministic functions and computed columns, consider:

SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON

CREATE FUNCTION uf_FormatAsNumeric ( @fld_value char(3) )
RETURNS int
WITH SCHEMABINDING 
AS
BEGIN
  SET @fld_value = CASE 
    WHEN @fld_value IS NULL THEN '0'
    WHEN ISNUMERIC(RIGHT( @fld_value, 1 )) = 0 THEN REPLACE( @fld_value, RIGHT( @fld_value, 1 ), '' )
    WHEN ISNUMERIC(SUBSTRING( @fld_value, 2, 1 )) = 0 THEN REPLACE(@fld_value,SUBSTRING( @fld_value, 2, 1 ), '' )
    WHEN ISNUMERIC(LEFT( @fld_value, 1 )) = 0 THEN REPLACE( @fld_value, LEFT( @fld_value, 1 ), '' )
    WHEN RTRIM(@fld_value) = '' THEN '0'
    ELSE @fld_value END 
  RETURN CAST( @fld_value AS INT )
END 

ALTER TABLE table1 ADD FormatAsNumeric AS dbo.uf_FormatAsNumeric( Col_X  )
CREATE INDEX idx_FormatAsNumeric ON table1( FormatAsNumeric )

SET QUOTED_IDENTIFIER OFF
SET ANSI_PADDING OFF

SELECT Col_X, FormatAsNumeric
  FROM table1
  ORDER BY FormatAsNumeric

Adjust the specific return value of the UDF as you prefer, it's the concepts I'd like to open your eyes to... HTH.