Extracting Substring from string

Debilon 431 Reputation points
2022-03-14T19:43:38.3+00:00

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 ?

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
{count} votes

5 answers

Sort by: Most helpful
  1. Michael Taylor 49,246 Reputation points
    2022-03-14T20:04:28.77+00:00

    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
    

  2. Naomi 7,361 Reputation points
    2022-03-14T21:36:57.503+00:00

    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


  3. Naomi 7,361 Reputation points
    2022-03-14T22:18:13.957+00:00

    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
    

  4. Erland Sommarskog 102.3K Reputation points
    2022-03-14T22:21:20.577+00:00

    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.

    0 comments No comments

  5. LiHong-MSFT 10,046 Reputation points
    2022-03-15T07:27:08.25+00:00

    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