Mixed string problem

Transact-SQL
1

I have a field that sorts like this:

10C1
10C10
10C11
10C2
1B1
1B10
1B2

I need it to sort like this:
1B1
1B2
1B10
10C1
10C2
10C10
10C11

Basically, the digits before the first letter needs sorted by number and then the digits after the letter needs sorted by the number.

2

Ok, after some research, I've built this......

    , 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.

3

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
1 Like
4

Alternative:

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)
;
1 Like
5

This code still sorts incorrect: Here is how it is sorting

11A29
11A3
11A30
11A31

The 11A3 should come after 11A2, not 11A29

Thanks you both for your help on this.

6

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]

7

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.

1 Like
8

Maybe I edited it wrong but it didn't work for me based on the results.

Capture

1 Like
9

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
10 
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
;
1 Like
11

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.

12

Thank you. I'm away from work now but will try this out in the morning.

13

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.

14

I'm not twisting any arms here for help and neither is the other 70% of questions asked here that don't supply useable data in their questions.

15

Works great! Thanks for your help with my issue!