July 23, 2017 at 9:04 am
CREATE TABLE #INPUT
(
ADDRESS_1 VARCHAR(200),
IS_PO_BOX BIT
)
INSERT INTO #INPUT Values ('PO BOX 123',0)
INSERT INTO #INPUT Values ('PO BOX 123 Washington st',0)
INSERT INTO #INPUT Values ('POSTOFFICEBOX 213 Texas st',0)
INSERT INTO #INPUT Values ('POSTOFFICEBOX',0)
INSERT INTO #INPUT Values ('PO BOX 222 Ph road',0)
INSERT INTO #INPUT Values ('123 MN road',1)
---OUTPUT
INSERT INTO #INPUT Values ('PO BOX 123',1)
INSERT INTO #INPUT Values ('PO BOX 123 Washington st',0)
INSERT INTO #INPUT Values ('POSTOFFICEBOX 213 Texas st',0)
INSERT INTO #INPUT Values ('POSTOFFICEBOX 111',1)
INSERT INTO #INPUT Values ('PO BOX 222 Ph road',0)
INSERT INTO #INPUT Values ('123 MN road',0)
I have provided input and expected output in above query, Here is my question
IF address_1 contains only PO BOX then IS_PO_BOX value should be "1"
IF Address_1 contains PO BOX and other text value then IS_PO_BOX value should be "0"
IF Address_1 doesn't contains PO BOX then IS_PO_BOX value should be "0"
Hope this helps, let me know if any questions, Thank you for help.
July 24, 2017 at 1:32 am
Here is a quick suggestion
😎
USE TEEST;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'tempdb..#INPUT') IS NOT NULL DROP TABLE #INPUT;
CREATE TABLE #INPUT
(
ADDRESS_1 VARCHAR(200),
IS_PO_BOX BIT
)
INSERT INTO #INPUT Values ('PO BOX 123',0)
INSERT INTO #INPUT Values ('PO BOX 123 Washington st',0)
INSERT INTO #INPUT Values ('POSTOFFICEBOX 213 Texas st',0)
INSERT INTO #INPUT Values ('POSTOFFICEBOX 111',0)
INSERT INTO #INPUT Values ('PO BOX 222 Ph road',0)
INSERT INTO #INPUT Values ('123 MN road',1)
SELECT
I.ADDRESS_1
,CASE
WHEN PATINDEX('PO BOX [0-9]%',I.ADDRESS_1) = 1 AND PATINDEX('%[0-9]%',REVERSE(I.ADDRESS_1)) = 1 THEN 1
WHEN PATINDEX('POSTOFFICEBOX [0-9]%',I.ADDRESS_1) = 1 AND PATINDEX('%[0-9]%',REVERSE(I.ADDRESS_1)) = 1 THEN 1
ELSE 0
END AS IS_PO_BOX
FROM #INPUT I;
Output
ADDRESS_1 IS_PO_BOX
---------------------------- -----------
PO BOX 123 1
PO BOX 123 Washington st 0
POSTOFFICEBOX 213 Texas st 0
POSTOFFICEBOX 111 1
PO BOX 222 Ph road 0
July 24, 2017 at 4:29 am
Working fine, Thanks for your help, have a good day,Thanks.
July 24, 2017 at 6:02 am
A different option:
SELECT
I.ADDRESS_1
,CASE WHEN I.ADDRESS_1 LIKE 'PO BOX [0-9]%' AND STUFF(I.ADDRESS_1, 1, 7, '') NOT LIKE '%[^0-9]%' THEN 1
WHEN I.ADDRESS_1 LIKE 'POSTOFFICEBOX [0-9]%' AND STUFF(I.ADDRESS_1, 1, 14, '') NOT LIKE '%[^0-9]%' THEN 1
ELSE 0 END
FROM #INPUT I;
July 24, 2017 at 9:00 am
SQL has a CASE expression, not CASE Statement!
You really need to read a book on basic database design and RDBMS. By definition, a table must have a key, but you have none, and no way to ever have a key. Column name should follow ISO 11179 rules; yours do not. This is SQL and not assembly language, so we don't use bit flags. You're also destroying other information by not having a proper address type in your design.
Finally, why are you not using a data scrubbing tool on your addresses? It's faster and cheaper and it's accurate. They will follow the CASS standards for the USPS (Google it).
Unfortunately, these days, the other important thing about an address scrubbing package is that you will not be criminally liable for it. 🙂
CREATE TABLE Addresses
(address_txt VARCHAR(200) NOT NULL PRIMARY KEY,
address_type CHAR(5) NOT NULL
CHECK (address_type IN ('box', 'street, ..));
You also might want to learn the table construction syntax for insertion statements. You're still doing it the way we did in the original Sybase product; one punch card at a time.
INSERT INTO Addresses
VALUES
('PO BOX 123', 'box'),
('123 Washington St', 'street'),
..
('123 MN Rd', 'street');
If you just want to kludge, instead of a real solution, then you can use the pattern index function to search for your string. I hope you want to be a better programmer than that though.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply