Jane Fonda
Jane Fonda

Reputation: 9

SQL Query help to retrieve rows with or without leading zeroes

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

Answers (3)

Stefanov.sm
Stefanov.sm

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')
  • Edit

The ! is there so that it will work correctly if you look for something like '20' for example.

Upvotes: 0

Avinash Pandey
Avinash Pandey

Reputation: 1

This query will help you

SELECT * FROM `customer` WHERE `OrderType` LIKE '%12%' OR `OrderType` LIKE '%81%'

Check here the result

Upvotes: 0

Raseena Abdul
Raseena Abdul

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

Related Questions