SQL: Query that finds the ID from a string

Question

Hi I'm starting to learn SQL and I was given a list of IDs to look for in a query. Here's the sample table.

However, the IDs I'm looking for in the database has extra strings on it for example in the database ID 123 is '000123'. How can I use the WHERE ID IN function to work on that?

Answer 1

You may use the like operator to see whether the specified Id contained in the ID column. for e.g.

select * from <tablename> where ID like '%123'  

Refer https://learn.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql?view=sql-server-ver16

One problem with this that you may get multiple records in the query, for eg, the above query will return all IDs ending with 123. To prevent this, instead of using the like clause, you can use the format method to format the ID to the neccessary string format and do an equal comparison. For. e.g.

select *  from articles where Format(Id, '0000000') = '0000123'  

Refer: https://learn.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql?view=sql-server-ver16

hope this helps

Answer 2

Hi @Sherlan Emmanuel Burgonia

How can I use the WHERE ID IN function to work on that?

It doesn't matter whether it has extra strings or not when you use WHERE ID IN.
Just type the full string along with the single quotes as follows: WHERE ID IN ('000123','000124','000125')

If you want to do some calculations or Boolean expressions via ID column.
Then you could use CAST to change ID from string to INT value, like this: WHERE CAST(ID AS INT) > 125
Refer to this doc: CAST and CONVERT (Transact-SQL)

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.