Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Query Wildcards

Status
Not open for further replies.

Debbiemtl

Technical User
Nov 3, 2020
4
CA
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......



 
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
 
Code:
 T0.ADDRESS2 LIKE '% AB[ ,]%'

NOT TESTED!!!!

Borislav Borissov
VFP9 SP2, SQL Server
 
Code:
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top