SQL Query Wildcards

[Debbiemtl]

Hi,

I am writing a query to return all Provinces from an address field. Our IT department prefer I do not use Parsname. so i am doing it this using a wildcard.

I am using the wildcard LIKE '% ON %' and T0.OINV and T1.INV12

My problem for instance is for example " ON ," sometimes it has " ON," or and for some reason it is not picking up some where it is " ON "

any ideas how better to use a wildcard in this scenerio ?

I am using the following:

SELECT
t0.docnum,
'invoice' as doctype
,T0.[DocDate] as 'Date'
,t2.itemcode as 'item No.'
,t2.dscription as 'Item Description'
,t0.cardcode as 'Customer Code'
,t0.cardname as 'Customer Name'
,T1.[StateS] as 'Ship-To Original Province',

CASE


when t1.States is null and T0.ADDRESS2 LIKE '% AB %' THEN 'AB'
when t1.States is null and T0.ADDRESS2 LIKE '% BC %' THEN 'BC'
when t1.States is null and T0.ADDRESS2 LIKE '% MB %' THEN 'MB'
when t1.States is null and T0.ADDRESS2 LIKE '% NB %' THEN 'NB'
when t1.States is null and T0.ADDRESS2 LIKE '% NL %' THEN 'NL'
when t1.States is null and T0.ADDRESS2 LIKE '% NT %' THEN 'NT'
when t1.States is null and T0.ADDRESS2 LIKE '% NS %' THEN 'NS'
when t1.States is null and T0.ADDRESS2 LIKE '% NU %' THEN 'NU'
when t1.States is null and T0.ADDRESS2 LIKE '% ON %' THEN 'ON' etc, etc, etc

ELSe T1.[StateS]
END AS 'Ship to Province'
--rest of my query......

 

[tacohunter]

Can you provide a sample data of an address that you are parsing with the wildcard?

Maybe something like

when t1.States is null and T0.ADDRESS2 LIKE '% AB %' or '% AB, %' THEN 'AB'

Chris AKA TacoHunter

 

[Debbiemtl]

Here is an example

 

[bborissov]

 T0.ADDRESS2 LIKE '% AB[ ,]%'

NOT TESTED!!!!

Borislav Borissov
VFP9 SP2, SQL Server

 

[Debbiemtl]

Thanks Borislav,

I will test.

Debbie

 

[bborissov]

DECLARE @Test TABLE (Fld1 varchar(200))
INSERT INTO @Test VALUES ('We are in ON and we are very happy')
INSERT INTO @Test VALUES ('We are in ON, that''s it')
INSERT INTO @Test VALUES ('We are in ON')
INSERT INTO @Test VALUES ('ON is the province were we are')

-- 3 different result based on what you want
SELECT * FROM @Test WHERE Fld1 LIKE '% ON[ ,]%'
SELECT * FROM @Test WHERE Fld1+' ' LIKE '% ON[ ,]%'
SELECT * FROM @Test WHERE ' '+Fld1+' ' LIKE '% ON[ ,]%'

Borislav Borissov
VFP9 SP2, SQL Server

 

[dhookom]

Any solution I would implement would probably begin with a table of two character values that can be easily maintained without all the complex expressions. Data belongs in your tables, not your code.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016