, cast(LEFT(sw.LOCATION, PATINDEX('%[A-Z]%',sw.LOCATION)-1) as integer) as [Number Before]
, cast(RIGHT(rtrim(sw.LOCATION), LEN(sw.LOCATION) - PATINDEX('%[A-Z]%',rtrim(sw.LOCATION))) as integer) as [Number After]
but I am getting this error........Invalid length parameter passed to the LEFT or SUBSTRING function.
Btw, that's also the best way to provide test values in your post, as useable data in a query, not just a splat of data on the screen.
SELECT sw.LOCATION
FROM (VALUES('10C1'),('10C10'),('10C11'),('10C2'),('1B1'),('1B10'),
('A23'),('A11'),('A'),('24B'),('15')
) AS sw(LOCATION)
CROSS APPLY (
SELECT PATINDEX('%[A-Z]%', sw.LOCATION) As letter_byte
) AS ca1
ORDER BY CASE WHEN letter_byte = 0 THEN sw.LOCATION ELSE RIGHT('0000' + LEFT(sw.LOCATION, ca1.letter_byte - 1), 5) +
SUBSTRING(sw.LOCATION, ca1.letter_byte, 1) + RIGHT('00000' + SUBSTRING(sw.LOCATION, ca1.letter_byte + 1, 5), 5) END
select yourfield
from yourtable
order by cast(left(yourfield,patindex('%[^0-9]%',yourfield)-1) as int)
,substring(yourfield,patindex('%[^0-9]%',yourfield),len(yourfield)-patindex('%[^0-9]%',reverse(yourfield))-patindex('%[^0-9]%',yourfield)+2)
,cast(right(yourfield,patindex('%[^0-9]%',reverse(yourfield))-1) as int)
;
Working with what I got so far, this works as it returns the locations in the proper order but it returns the two "Helper" columns that I don't need plus it errors out on locations that don't have (number,Letter,Numer) order.
I'm sort of new at SQL so I'm sure there is a way to hide the helper columns, but still use them to sort. Then, if an error happens dealing with the locations not in (number,Letter,Numer) order, just return the location and stick them down at the top or bottom.
select
sw.LOCATION
, SUBSTRING(sw.location,PATINDEX('%[A-Z]%',sw.LOCATION),1) as [Letter]
, cast(LEFT(sw.LOCATION, PATINDEX('%[A-Z]%',sw.LOCATION)-1) as integer) as [Number_Before]
, cast(RIGHT(rtrim(sw.LOCATION), LEN(sw.LOCATION) - PATINDEX('%[A-Z]%',rtrim(sw.LOCATION))) as integer) as [Number_After]
from STOCKWH sw
Where sw.LOCATION like '10C%'
order by [Number_Before], [Letter], [Number_After]
That's exactly what I did with the code I posted above and it sorts
11A3
11A29
11A30
11A31
just fine for me. I'm not sure what else you are really trying to do.
You did not give any examples in your original post without a letter, implying that it couldn't happen, so I didn't spend a lot of time on logic for it. [And you still refuse to provide data in a useable format, such as VALUES, rather than just an empty list of data.]
SELECT sw.LOCATION
FROM (VALUES('10C1'),('10C10'),('10C11'),('10C2'),('1B1'),('1B10'),
('A23'),('A11'),('A'),('24B'),('15')
) AS sw(LOCATION)
CROSS APPLY (
SELECT PATINDEX('%[A-Z]%', sw.LOCATION) As letter_byte
) AS ca1
ORDER BY CASE WHEN letter_byte = 0 THEN RIGHT('00000' + sw.LOCATION, 5) ELSE RIGHT('0000' + LEFT(sw.LOCATION, ca1.letter_byte - 1), 5) +
SUBSTRING(sw.LOCATION, ca1.letter_byte, 1) + RIGHT('00000' + SUBSTRING(sw.LOCATION, ca1.letter_byte + 1, 5), 5) END
select yourfield
from yourtable
order by cast(case
when yourfield like '[0-9]%[^0-9]%[0-9]'
then left(yourfield,patindex('%[^0-9]%',yourfield)-1)
else 0
end as int
)
,case
when yourfield like '[0-9]%[^0-9]%[0-9]'
then substring(yourfield,patindex('%[^0-9]%',yourfield),len(yourfield)-patindex('%[^0-9]%',reverse(yourfield))-patindex('%[^0-9]%',yourfield)+2)
else ''
end
,cast(case
when yourfield like '[0-9]%[^0-9]%[0-9]'
then cast(right(yourfield,patindex('%[^0-9]%',reverse(yourfield))-1) as int)
else 0
end as int
)
,yourfield
;
Did you eat breakfast this morning? If I'm upsetting you with my question, move along, I'll get the answer from someone else. I'm sorry I don't know how to supply useable data on here.
OK, I'll move along. Good luck with your qs. Do realize, though, that you are asking people to do free work for you, therefore it's minimum courtesy to provide useable data so that everyone who donates their own time helping you doesn't have to re-code the test data for themselves.