December 31, 2014 at 9:01 pm
I have a I have a column that had the country [Country] but also a column that has the state and country [Address2]. I need to trim the Country from the [Address2] column. Any suggestions knowing that the Country can have 1,2,3 or more words.?
Country Address2 Resulting Column
Australia Sydney Australia Sydney
USA Washington USA Washington
United Kingdom London United Kingdom London
January 1, 2015 at 4:45 am
Quick suggestion, use a "country" table, otherwise it is not possible to do this, consider the example below.
😎
USE tempdb;
GO
SET NOCOUNT ON;
/* Country table */
DECLARE @COUNTRY TABLE
(
COUNTRY_NAME NVARCHAR(128) NOT NULL
,COUNTRY_3166 CHAR(2) NOT NULL
);
/* Address table */
DECLARE @ADDRESS TABLE
(
ADDRESS_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
,ADDRESS_TEXT NVARCHAR(150) NOT NULL
);
/* Sample data */
INSERT INTO @COUNTRY(COUNTRY_NAME,COUNTRY_3166)
VALUES (N'Australia','AU')
,(N'USA','US')
,(N'United Kingdom','GB')
,(N'Kazakhstan','KZ');
INSERT INTO @ADDRESS(ADDRESS_TEXT)
VALUES (N'Australia Sydney')
,(N'USA Washington')
,(N'United Kingdom London')
/* Simple cleaning / trimming query */
SELECT
AD.ADDRESS_ID
,AD.ADDRESS_TEXT
,CT.COUNTRY_NAME
,CT.COUNTRY_3166
,LTRIM(REPLACE(AD.ADDRESS_TEXT,CT.COUNTRY_NAME,N'')) AS ADDRESS_PART_TWO
FROM @ADDRESS AD
CROSS APPLY @COUNTRY CT
WHERE CHARINDEX(CONCAT(CT.COUNTRY_NAME,NCHAR(32)),AD.ADDRESS_TEXT,1) = 1;
Results
ADDRESS_ID ADDRESS_TEXT COUNTRY_NAME COUNTRY_3166 ADDRESS_PART_TWO
----------- ----------------------- ---------------- ------------ -----------------
1 Australia Sydney Australia AU Sydney
2 USA Washington USA US Washington
3 United Kingdom London United Kingdom GB London
January 1, 2015 at 12:40 pm
Just a simple look at what is asked:
/*
Country Address2 Resulting Column
Australia Sydney Australia Sydney
USA Washington USA Washington
United Kingdom London United Kingdom London
*/
create table dbo.Addresses (
Address2 varchar(128),
Country varchar(128)
);
insert into dbo.Addresses
values ('Sydney Australia','Australia'),('Washington USA','USA'),('London United Kingdom','United Kingdom');
select
Address2,
left(Address2, patindex('%' + Country + '%', Address2) - 1) Address2_Trimmed
from
dbo.Addresses;
drop table dbo.Addresses;
January 1, 2015 at 1:05 pm
Lynn Pettis (1/1/2015)
Just a simple look at what is asked:
Oops, didn't read the question properly:pinch:
😎
January 1, 2015 at 1:30 pm
Use REPLACE()
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 1, 2015 at 1:34 pm
ChrisM@home (1/1/2015)
Use REPLACE()
Like this:
/*
Country Address2 Resulting Column
Australia Sydney Australia Sydney
USA Washington USA Washington
United Kingdom London United Kingdom London
*/
create table dbo.Addresses (
Address2 varchar(128),
Country varchar(128)
);
insert into dbo.Addresses
values ('Sydney Australia','Australia'),('Washington USA','USA'),('London United Kingdom','United Kingdom');
select
Address2,
left(Address2, patindex('%' + Country + '%', Address2) - 1) Address2_Trimmed1,
replace(Address2, Country, '') Address2_Trimmed2
from
dbo.Addresses;
drop table dbo.Addresses;
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply