Reputation: 9
Can someone help me with SQL query. I have a below table.
Table: Customer
ID | Name | OrderType |
---|---|---|
1 | Brate | 012 |
2 | Tom | 081 |
3 | Shane | 12 |
4 | Bill | 123 |
5 | David | 051 |
6 | Reid | 81 |
7 | shohan | 08122 |
8 | Amy | 323 |
Requirement is that I need to get all the rows which has ordertype in (012x,081x) with or without leading zeroes. So on above table, the SQL query should get me rows for id 1,2,3,4,6 and 7) Can someone help me with the sql query to get this result? appreciate your help. 6
Upvotes: 0
Views: 304
Reputation: 13049
Trim zeroes, pick the first 2 characters and then compare.
select *
from Customer
where left(trim('0 ' from OrderType + '!'), 2) in ('12', '81')
The !
is there so that it will work correctly if you look for something like '20' for example.
Upvotes: 0
Reputation: 1
This query will help you
SELECT * FROM `customer` WHERE `OrderType` LIKE '%12%' OR `OrderType` LIKE '%81%'
Upvotes: 0
Reputation: 536
This should give you what you are looking for if OrderType is always numeric
select *
from cust
where cast(cast(OrderType as int) as varchar) like '81%'
or cast(cast(OrderType as int) as varchar) like '12%'
Cast it to int to remove all the leading zeroes and cast it back to varchar
Upvotes: 1