Hy everybody.

I hope someone could give me some help.

I have the following rows in the database (varchar type):
tableColumn
-------------------
12text
1text
123text


I have to get the rows sorted like this:
1text
12text
123text

I just need a query...or any ideas? I use stored procedures......


HELP!!!!!!!!


Floriana P

The example is not exactly what I had in mind:

Here is a new one:

1atext
12text
12atext
1text


I need the rows ordered like that:

1atext
12atext
1text
12text

. The result I get is:
1text
1atext
12text
12atext


HELP!!!







Floriana P

That is how the rows will be ordered if ordering on that column. To change the order you need to order on a different column. I would suggest an identity column.

And just how would ordering on an identity column solve the OP's problem? What relationship would the value of the identity column on a given row have to the column to be sorted so that the the rows would be "properly" ordered? What if a new row were inserted with a sort column value in between existing rows. The identity value would be "far away" from the existing rows, so how would that help?

To the OP:

The problem is that the column is a composite column - a value comprised of two other values. This is a violation of first normal form and this design is likely to be doomed to ongoing difficulties. The fact that you want to sort in a particular way on two distinct portions of the column tells me you have attached meaning to each distinct piece. Why did you combine these meanings into a single value?

Suppose instead that the original column was split into two columns: the first containing the numeric portion of the original column, and the second containing the string portion. Then, if I understand your desired ordering, it would be by the string portion and then the numeric portion within each string value.

Separate them out; things will be much easier.

If you really can't (why?), you could (if SQL Server 2000), write two user defined functions: one to parse off and return the leading numeric portion, and another the string portion. You would then order by the string function, then the numeric function value within the string value.

In SQL Server 2000, you can use the SUBSTRING and ISNUMERIC functions to extract off the leading numeric characters of the input column then CAST them to a numeric value.

Using functions this way would be very slow. It would be orders of magnitude faster if the column values were split correctly in the first place...

Good luck.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

I receive the value of the column from my client (there are product codes). I import the products in my database and then in some form he wants to see those codes - but ordered the way I write in the first topic.

I tried using regex and user defined functions - in some way the same you suggested, but the order is not exactly the order my client wants (3text goes before 31text but after 3)......


Thanks.


Amalia

Floriana P

Can you tell me the rule which describes the ordering, rather than just giving examples?

It looks like you want the ordering by the text portion of the column, then by the numeric portion of the column, within a given text value. Note that an empty string is a perfectly good text value, so it would sort first. Is the numeric portion to be in numeric order or string order? That is, does 3 come before or after 10?

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

I want to sort:
- the text
- the numbers (that can miss of some row values) - but not in their value order, but in their ...ASCII order I guess....1text goes in front of 11text.The problem is that the values of my rows can be: "11text-22text texttext".

Any way, I started parsing every value of every row - but it is runs slow (and on some values not very exact either).

Floriana P

Well, as I said in an earlier response,
You need to find a way to "fix" this, or we'll be having conversations like this from now one...

Since you said you were importing the data which is encoded this way into your database, you may want to consider splitting this composite column into two columns in your database table during the import. As each row is imported, parse off any leading numeric characters and store them into one column, defined as a character data type. The remaining text (once you encounter a nonnumeric character) goes into a second column.

Then you can do any query on this table and order by the second column (the text) then the first (the numeric portion, but in string order).

This way, you slow the import down (which you only do once in a while), but your queries run faster. (And my normalization sensibilities are no longer offended ;).)

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

I suggest using other fields and breaking up the data. Use logic to pull out the number in the beginning into one filed, and the character values to another. Then you can vary your sort without altering the data you were sent in this field. If thats what you want I need to know how many numberic values you can have before getting into the alpha characters.

another suggestion is to not accept cr$p data and force your source to send data in a more reasonable way.