So I get the data from my server and the mvmt number consists of 3 characters and 4 digits (EX: CVG5694) or unless they start when it starts with X because it's 1 character (the letter x), and 5 digits (ex: X12051). This code worked for me until I got the error,

Conversion failed when converting the varchar value '5005A' to data type int.

I assume because it has the character A in the end. Anyone know how to fix?

SQL_Query = pd.read_sql_query('''SELECT[MVMT_DT], 
[MVMT_NUMBER],
CAST(RIGHT([MVMT_NUMBER], PATINDEX('%[0-9][^0-9]%', REVERSE([MVMT_NUMBER])+' ')) 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)]```