Extracting Substring from string

Question

Hi guys

I have a list of Names that have a legal definition as part of them
ET AL
ET UX
sometimes it will show as
ETAL / ETUX

the entire string looks like FirstName, LastName MI ET UX

I need to separate the legal definition and insert it to another column.

I tried right and left
select names, right(names,CHARINDEX(' ET',names))

but that gives me obviously right and left.

Any Idea ?

Answer 1

What version of SQL Server are you running? Newer versions of SQL have better string support but ultimately this is still going to be painful. It is also highly dependent upon how reliable your strings are going to be. Here's something that would work in SQL 2016 and above.

DECLARE @tests TABLE (value VARCHAR(100))
INSERT INTO @tests (value) VALUES 
('Bob, Smith A ET UX'),
('June, Smith A ET AL'),
('Mark, Smith A ETAL AL'),
('Susan, Smith A ETUX')

SELECT *, TRIM(SUBSTRING(value, CHARINDEX(' ET', value, 0), 1000)) FROM @tests

Answer 2

Try (thanks cooldadtx for the sample):

DECLARE @tests TABLE (value VARCHAR(100))
INSERT INTO @tests (value) VALUES
('Bob, Smith A ET UX'),
('June, Smith A ET AL'),
('Mark, Smith A ETAL AL'),
('Susan, Smith A ETUX'),
('David Browne MS')
SELECT *, LTRIM(RTRIM(SUBSTRING(value, NULLIF(CHARINDEX(' ET', value, 0),0), 1000))) FROM @tests

Answer 3

I ran the above on the SQL 2016 server which doesn't have TRIM function yet. I used a common trick to turn 0 into NULL (using NULLIF function), so SUBSTRING function would not generate an error. If we want to keep the info before that part, then I would probably use multiple steps technique, e.g.

SELECT t.*, 
CASE WHEN pos.ET_Pos > 0 THEN LEFT(t.value, pos.ET_Pos - 1) ELSE t.value END AS [FullNameWithoutETAll],
LTRIM(RTRIM(SUBSTRING(t.value, NULLIF(pos.ET_Pos,0), 1000))) AS ETAll FROM @tests t
CROSS APPLY (SELECT CHARINDEX(' ET', value) AS ET_Pos) pos

Answer 4

Working from the previous examples:

DECLARE @tests TABLE (value VARCHAR(100))
INSERT INTO @tests (value) VALUES
('Bob, Smith A ET UX'),
('June, Smith A ET AL'),
('Mark, Smith A ETAL AL'),
('Susan, Smith A ETUX'),
('David Browne MS')

; WITH CTE AS  (
   SELECT value, ETpos = charindex(' ET', value)
   FROM   @tests
)
SELECT IIF(ETpos > 0, trim(left(value, ETpos)), trim(value)) AS nmae,
       IIF(ETpos > 0, trim(substring(value, ETpos, len(value))), NULL) AS legal
FROM   CTE

I guess the reason that Naomi used rtrim + ltrim rather than trim is simply old habit. trim was not introduced until SQL 2017. The other two have been around forever.

But your last snippet resorts in an error, and i don't understand why.

Because there is a plain syntax error. Cooldadtx is trying to use a boolean expression as a value, but that is not legal T-SQL.

Answer 5

Hi @Debilon

One additional question is how do I distinguish between valid ET codes and real names such as Ethel, Ethan, ETTE, Etta etc. ?

Check this:

DECLARE @tests TABLE (value VARCHAR(100))  
INSERT INTO @tests (value) VALUES  
('Bob, Smith A ET UX'),('June, Smith A ET AL'),('Mark, Smith A ETAL AL'),  
('Susan, Smith A ETUX'),('David, Browne MS'),  
('Mars, Ethan A ET UX'),('Ethel, Etta A ET AL')  
  
SELECT value,CASE WHEN C.ETPos > 0   
                  THEN LEFT(value,C.ETPos - 1)   
   ELSE value END AS [Name],  
    TRIM(SUBSTRING(REPLACE(value,', ',','), NULLIF(C.ETPos,0), 1000)) AS Legal  
FROM @tests t  
CROSS APPLY (SELECT CHARINDEX(' ET',REPLACE(value,', ',',')) AS ETPos)C  

Best regards,
LiHong