'How to get pin codes without zeros in it?
Write a query to display the customer_id,customer full name ,city,pincode,and order details (order id, product class desc, product desc, subtotal(product_quantity * product_price)) for orders shipped to cities whose pin codes do not have any 0s in them. Sort the output on customer name and subtotal. (52 ROWS) [NOTE: TABLE TO BE USED - online_customer, address, order_header, order_items, product, product_class].
These are the attributes of each table:
Address- ADDRESS_ID, ADDRESS_LINE1,ADDRESS_LINE2,CITY,STATE,PINCODE,COUNTRY
ONLINE_CUSTOMER- CUSTOMER_ID, CUSTOMER_FNAME, CUSTMER_LNAME, CUSTOMER_EMAIL,CUSTOMER_PHONE, ADDRESS_ID, CUSTOMER_CREATION_DATE,CUSTOMER_USERNAME, CUSTOMER_GENDER
ORDER_HEADER- ORDER_ID, CUSTOMER_ID, ORDER_DATE, ORDER_STATUS, PAYMENT_MODE, PAYMENT_DATE, ORDER_SHIPMENT_DATE,SHIPPER_ID
ORDER_ITEMS- ORDER_ID, PRODUCT_ID, PRODUCT_QUANTITY
PRODUCT- PRODUCT_ID, PRODUCT_DESC, PRODUCT_CLASS_CODE, PRODUCT_PRICE, PRODUCT_QUANTITY_AVAIL, LEN, WIDTH, HEIGHT, WEIGHT
PRODUCT_CLASS- PRODUCT_CLASS_CODE, PRODUCT_CLASS_DESC
Can anyone help me on how to filter pincodes without any zeros in it and order status as shipped.
Solution 1:[1]
Test if no zeros exist
where length(replace(pincode,'0','')) = length(pincode)
or
where instr(pincode,'0') = 0
or you could use regex or test valid pincodes by downloading and validating
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | P.Salmon |