[RESOLVED] SQL Server - Possible to split a string, sort both parts, and put them back together?

[themindgoblin]

Out of curiosity, I am wondering if it is possible to split a string into two parts, sort each of them, then put them back together, sorted?

Example: A field in one of our tables is full of strings that are all basically the same format. One letter, and then one to three numbers, i.e. - E100, S54, E5, etc.

I would love to be able to sort by this column, but I'm sure you already know the issue. E36, E4, E44, E5, E50, and so on. I am aware of the split function, and casting to numeric to sort the numbers, and concatenating them, but I'm just not entirely sure how I would go about this.

For info: I'm trying to do this in a single select statement, but I'm guessing that might not be possible?

Would I be able to use the split function, order by those created columns, and have a new column that is comprised of the concatenation of those two columns, or would this be something I would need to make a stored procedure for, before using a select statement?

[digitalShaman]

If i understand it right, this would just be an ORDER BY

SELECT * FROM tbl
ORDER BY CAST(SUBSTRING(field,2,999) AS int)

be aware that this will only work if there are only digits after the letter. If there is just one row that has some other chars after the first letter, it will cause an error becaue the substring cannot be cast to an integer

[themindgoblin]

If i understand it right, this would just be an ORDER BY

SELECT * FROM tbl
ORDER BY CAST(SUBSTRING(field,2,999) AS int)

be aware that this will only work if there are only digits after the letter. If there is just one row that has some other chars after the first letter, it will cause an error because the substring cannot be cast to an integer

It's a little more complex than that, unfortunately.

Some of the strings are "PM56", "AE47", "E55", etc. There is sometimes one, or two, or even three letters before any numbers. The numbers can be 1, 2 or 3 digits.

I've made some headway but ANY examples I have found use either Left, or SubString combined with PatIndex to split the string. This works fine, except I CANNOT subtract ANYTHING from the index or it throws me an error.

SUBSTRING(Location, 1, PATINDEX('%[0-9]%', Location) - 1)

This throws the error: "Invalid length parameter passed to the LEFT or SUBSTRING function."

Every example I've seen uses this, and presumably because you NEED to subtract one from it to only grab the alpha characters, or at least I do in my scenario. Using the above line WITHOUT subtracting one from the Pattern Index, I get "A1", or "E5", or "AE6" etc. I get all the letters, and the first number.

I could just be a dumbass, but WHY will it not let me subtract one from the Pattern Index so it leaves off the first number?


EDIT: I am a dumbass. There were a few values in my Location column that were ONLY numeric, and a few that were ONLY alpha. So the index was starting at 0 and throwing an error when trying to subtract one from it.

Either way, I have it working now. Here's my ORDER BY:

ORDER BY SUBSTRING(Location, 1, PATINDEX('%[0-9]%', Location) - 1), CAST(SUBSTRING(Location, PATINDEX('%[0-9]%', Location), LEN(Location)) AS int)

This splits up the string and sorts them correctly. I am not worrying about joining them back together because I realized I do not need that. I simply needed them to sort in actual order based on the letter prefix and then numbers, but I can obviously grab the original value from the original column.