SQL Query that looks for ID number

Question

I was given a list of ID in string format and extract information from the database based on the ID number. However, these IDs in the database start with either '001' or '002'. Initially, I'm using WHERE ID IN ('0016085','0026085') to find it but I think there's a more efficient way instead of hardcoding both scenarios.

TABLE

Answer 1

Hi @Sherlan Emmanuel Burgonia
You could use RIGHT or SUBSTRING functions to ignore first three digits. Check the sample below:

CREATE TABLE #TABLE(ID VARCHAR(20))  
INSERT INTO #TABLE VALUES  
('00160885'),('00260885'),('00133263'),('00233263')  
  
SELECT ID  
FROM #TABLE  
WHERE RIGHT(ID,LEN(ID)-3) = 60885  
  
SELECT ID  
FROM #TABLE  
WHERE SUBSTRING(ID,4,LEN(ID)) = 60885  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

The right thing is to learn the basic about data types and choose proper data type for your table columns. Don't start with manipulate your data and extract numbers from string type of data.

Answer 3

WHERE ID LIKE '00[12]6085'