How to do sub-string calculations in SQL?

I am working with a table full of customer online orders. Each order has a company code which can be either ’09’, ’07’, ’04’, ’03’, and ’01’. Each order has an order number that looks something like this ‘90180628’. You can tell which company an order is from by the first number of it’s order number. So that order is for company 09 and this ‘31610629’ would be an order number for company ’03’

Before I was accessing a table that had direct knowledge of which company each order came from so my WHERE clause looked like this.

WHERE 
    (O.Company_Code = @CompanyCode OR @CompanyCode IS NULL) AND 
    (O.Division_Code = @DivisionCode OR @DivisionCode IS NULL) AND
    o.Customer_Number = 'ecom2x' AND 
    o.Customer_Purchase_Order_Number not like '%bulk%' AND
    o.DateRecordModified BETWEEN @FromDate AND DATEADD(dayofyear, 1, @ToDate)

Where @CompanyCode is the parameter the user selects (since this is a report) and so on.

However, now I am using a different table and I can’t access O.Company_Code so I have to check the company code against the order number.

So it would be something like this

Where SH.Packslip like 'substring(@CompanyCode,2,1)%'

That is psudo code that doesn’t work but that is the idea. Does anyone know how to turn that into functional code? Maybe I have to do it in two parts where the first part I declare a new variable and set it equal to the second character substring and then I check if the packslip starts with that new variable (since I need to get rid of the 0 in the 09)

Answer

you can try this.

Where SH.Packslip like substring(@CompanyCode,2,1) + '%'

Advertisement

Answer