SQL Select instance of substring in column to get right salutation from address-CodePudding

I have a large dataset where salutations are only in an address line, not in a salutation/prefix column, and I need to select them separately.

I've tried queries like WHEN type address1 LIKE '%Ms' THEN 'Ms'' but this is obviously not working when address has multiple salutations.

Is there a to select the nth instance of an array of supplied values ('Mr', 'Mrs' etc..) based on a different column, Eg: Select 2nd instance of matching value in array when column is LIKE 'secondary%'?

Data

name	lname	address1	type
Sarah	Cho	Ms Sarah Cho	primary account
Mark	Reed	Mr Mark and Mrs Kim Reed	primary account
Kim	Reed	Mr Mark and Mrs Kim Reed	secondary account

Desired output

prefix	fname	lname
Ms	Sarah	Cho
Mr	Mark	Reed
Mrs	Kim	Reed

CodePudding user response：

If all you want to do is get the leading salutation from the address, you may use:

SELECT SUBSTRING(address1, 1, CHARINDEX(' ', address1) - 1) AS prefix,
       name AS fname, lname
FROM yourTable;

CodePudding user response：

This would do the trick:

-- Sample data
DECLARE @t TABLE (
  FName    VARCHAR(30),
  LName    VARCHAR(30),
  address1 VARCHAR(100),
  AccType  VARCHAR(20));

INSERT @t
VALUES
('Sarah', 'Cho',  'Ms Sarah Cho',                'primary account'),
('Mark',  'Reed', 'Mr Mark and Mrs Kim Reed',    'primary account'),
('Kim',   'Reed', 'Mr Mark and Mrs Kim Reed',    'secondary account'),
('Fred',  'Smith','Mr Fred and Mrs Betty Smith', 'Primary account'),
('Betty', 'Smith','Mr Fred and Mrs Betty Smith', 'secondary account');


-- Solution
SELECT
  Prefix = IIF(t.AccType = 'primary account',
           RTRIM(LEFT(t.address1,3)),
           SUBSTRING(sec.AcctName,1,pfx.Pos-1)),
  FName  = t.FName,
  LName  = t.LName
FROM   @t AS t
CROSS APPLY (VALUES(SUBSTRING(t.address1,
                    CHARINDEX(' and ',t.address1) 5,100))) AS sec(AcctName)
CROSS APPLY (VALUES(CHARINDEX(' ',sec.AcctName)))          AS pfx(Pos);

Returns:

Prefix  FName     LName
------- --------- ------------------------------
Ms      Sarah     Cho
Mr      Mark      Reed
Mrs     Kim       Reed
Mr      Fred      Smith
Mrs     Betty     Smith