SQL Server : SUBSTRING from column

Question

I am trying to retrieve some information from our database. Specifically serial numbers. I need to utilize the SUBSTRING function to do this however, I am really stuck. I need to get all serial numbers who's 13th digit is a 0 and 14th digit is a 1. Since the serial numbers can change at anytime I thought checking the CustomerLotID columns for a 13th digit 0 and 14th digit 1 would be the best option however I am having some issues doing this. Please note that the 1, 2, '2/28/2017' after the select statements are for testing only.

SELECT  
    1, 2, '2/28/2017', '06:00', TX.OperationID, FriendlyName,
    COUNT(DISTINCT(CustomerLotID)) AS SerialNumber
FROM    
    fsWIPTxHist Tx 
INNER JOIN 
    FactoryServerMES.dbo.fsOperation O ON TX.OperationID = O.OperationID
WHERE   
    customerlotid = SUBSTRING('0',13,1) --<> '5D25984G14120%'
    AND (TX.DateCreated BETWEEN '2/28/2017 10:00:00' AND '2/28/2017 10:59:59') 
    AND (Status = 'Avail')
    AND TX.OperationID IN (3100) 
GROUP BY 
    TX.OperationID, FriendlyName

WHERE SubString(customerlotid, 13, 2) = '01' doesn't work for you? Did you consider reading the documentation for SubString? — Ken White
– Ken White, Commented Mar 2, 2017 at 1:23
I did read the documentation on MSDN. However, I may have misunderstood the syntax. Sorry if I wasted time for simple stuff. I think what confused me was the expression part of it. Thank you kindly for your help. — TAdams79
– TAdams79, Commented Mar 2, 2017 at 2:29

Renats Stozkovs · Accepted Answer · 2017-03-02 01:23:57Z

2

Try this:

WHERE SUBSTRING(customerlotid ,13,2) = '01'

answered Mar 2, 2017 at 1:23

Renats Stozkovs

2,63510 gold badges24 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server : SUBSTRING from column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related