Extract City Name from a String-CodePudding

I have multiple companies, with a set of locations that are a string. Each have different numbers of spaces, but all end in a city name (which also contain a space occasionally).

My hope is that I can write SQL that results in only the city names in a new column. The data would resemble these two columns

I've attempted RIGHT(CLIENTNAME, CHARINDEX(' ', REVERSE(CLIENTNAME) ' ') - 1 but that is not working. Is this possible?

Thanks!

CodePudding user response：

Please try the following solution.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE  (ID INT IDENTITY PRIMARY KEY, Location  VARCHAR(50));
INSERT INTO @tbl VALUES
('Company AB Roseville'),
('Company AB Des Moines'),
('Company CDE Houston'),
('Company CDE Los Angeles'),
('Company AB Salt Lake City');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = SPACE(1);

SELECT t.*
    , City = c.query('data(/root/r[position() gt 2])').value('text()[1]', 'VARCHAR(50)')
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA['   
      REPLACE(Location, @separator, ']]></r><r><![CDATA[')   
      ']]></r></root>' AS XML)) AS t1(c);

Output

ID	Location	City
1	Company AB Roseville	Roseville
2	Company AB Des Moines	Des Moines
3	Company CDE Houston	Houston
4	Company CDE Los Angeles	Los Angeles
5	Company AB Salt Lake City	Salt Lake City

CodePudding user response：

This may also work. You'd need to go in and try to find other examples like Des Moines and you can add them to the CASE expression, and it could get a little lengthy, but this might work for you. It will need more testing, but with my limited testing, it appears to do it.

drop table if exists #test

create table #test (name varchar(50))
insert into #test values
('Company AB Roseville'),
('Company AB Des Moines'),
('Company CDE Houston')


select name=case when name like '