How to filter with different lengths

Question

So I’m trying to get the data from my server and the mvmt number consists of 3 characters and 4 digits (EX: CVG5694) but its datatype is a string so I have to use the cast query and just get the last 4 digits. This gives me a column of integers that has the last 4 digits. However there are trucks that I need to get but it has a different format as having 1 character (the letter x), and 5 digits (ex: X12051). This is a problem because I'm trying to filter out numbers less than 5000. And because I get the last 4 digits it filters out the ones with 1 character and 5 digits (EX: x12051 becomes 2051 in the movement column and gets filtered. Do you know how I could get trucks with numbers such as X12051 in my filter?

Below is some of my code:

SQL_Query = pd.read_sql_query('''SELECT[MVMT_DT], 
[MVMT_NUMBER],
CAST(RIGHT(MVMT_NUMBER,4) as int) as movement,
[MVMT_TYPE], 
[OPERATOR],
[EQUIPMENT],
[ORIG],
[DEST],
[MVMT_STATUS],

CASE WHEN  [GROSS_WEIGHT_(KG)] < 0 THEN 0 ELSE [GROSS_WEIGHT_(KG)] END AS [GROSS_WEIGHT_(KG)],
CASE WHEN [NET_WEIGHT_(KG)]< 0 THEN 0 ELSE [NET_WEIGHT_(KG)] END AS [NET_WEIGHT_(KG)],
CASE WHEN [NMBR_ULDS] < 0 THEN 0 ELSE [NMBR_ULDS] END AS [NMBR_ULDS],
CASE WHEN [NMBR_POS] < 0 THEN 0 ELSE [NMBR_POS] END AS [NMBR_POS]


FROM PATH
WHERE [F-T-O] = 'T'
  AND ORIG IN ('CVG', 'CVG CRN', 'MIA', 'MIA GTW', 'LAX', 'LAX GTW', 'JFK', 'JFK GTW', 'ORD', 'ORD GTW')
  AND MVMT_TYPE IN ('O/XL', 'O/XL/AH', 'T/XL', 'T/XL/AH', 'CL/AH', 'O/AH', 'T/AH')
  AND [MVMT_NUMBER] NOT LIKE '%AMZ%'
  AND [MVMT_NUMBER] NOT LIKE '%A0%'
  AND [MVMT_NUMBER] NOT LIKE '%K0%'
  AND [MVMT_NUMBER] NOT LIKE '%A1%'
  AND [MVMT_NUMBER] NOT LIKE '%K1%'
  --AND RIGHT([MVMT_NUMBER], 4) <= 5000
  AND MVMT_DT = '2021-12-06'  --DATEADD(DAY, -2, GETDATE()) AND DATEADD(DAY, -1, GETDATE())''',conn_)

CXL_Filter = ['O/XL', 'O/XL/AH', 'T/XL', 'T/XL/AH']
Ad_Hoc_Filter = ['CL/AH', 'O/AH', 'T/AH']

CXL_CVG = SQL_Query[SQL_Query.MVMT_TYPE.isin(CXL_Filter) & (SQL_Query['ORIG'] == 'CVG') & (SQL_Query['movement'] >= 5000)]
CXL_CVG_CRN = SQL_Query[SQL_Query.MVMT_TYPE.isin(CXL_Filter) & (SQL_Query['ORIG'] == 'CVG CRN') & (SQL_Query['movement'] >= 5000)]
Ad_Hoc_CVG = SQL_Query[SQL_Query.MVMT_TYPE.isin(Ad_Hoc_Filter) & (SQL_Query['ORIG'] == 'CVG') & (SQL_Query['movement'] >= 5000)]
Ad_Hoc_CVG_CRN = SQL_Query[SQL_Query.MVMT_TYPE.isin(Ad_Hoc_Filter) & (SQL_Query['ORIG'] == 'CVG CRN') & (SQL_Query['movement'] >= 5000)]```

https://stackoverflow.com/questions/11333078/sql-take-just-the-numeric-values-from-a-varchar/11333169 - you could try a solution from here, Sean's answer looks the easiest to implement — procopypaster, Dec 10 '21 at 03:05
alternatively, if that isn't working the way you'd like, since you aren't doing a group by, you could use python to solve for this and then remove items <= 5000 in your python selection, or drop all rows prior to that - https://stackoverflow.com/questions/1249388/removing-all-non-numeric-characters-from-string-in-python — procopypaster, Dec 10 '21 at 03:07

LukStorms · Accepted Answer · 2021-12-10T04:45:12.277

You can get the whole number from MVMT_NUMBER

... 
CAST(RIGHT([MVMT_NUMBER], PATINDEX('%[0-9][^0-9]%', REVERSE([MVMT_NUMBER])+' ')) as INT) as movement, 
...

Test

select 
  MVMT_NUMBER
, CAST(RIGHT([MVMT_NUMBER], PATINDEX('%[0-9][^0-9]%', REVERSE([MVMT_NUMBER])+' ')) as INT) as movement
from (VALUES ('ABC5444'),('X12345'),('ABCD'),('1234')) val([MVMT_NUMBER])

MVMT_NUMBER	movement
ABC5444	5444
X12345	12345
ABCD	0
1234	1234

How to filter with different lengths

1 Answers1