How to select a variable length string from between two known strings in SQL Server in a VARCHAR(MAX) where some columns don't have applicable strings

Question

Using SQL Server 2012, I need to get from this example

ColumnName    
--------------------------------
 Enroll to: Carol Goals are many and varied  
 characters that don't include desired results
 Enroll to: Jan Levinson Goals will be discussed at first encounter
 Enroll to: Stephon-Anderson   Goals none
 NULL
 Enroll to:   David   Goals  --Note uneven spaces, Need David

to extract the column to look like:

Name    
-----------
Carol
NULL   
Jan Levinson   
Stephon-Anderson 
NULL 
David

This code got me pretty close to the results I was looking for, but sometimes trimmed the name incorrectly.

Select 
CASE WHEN AssignedTo like '%Enroll To:%' THEN  SUBSTRING(AssignedTo, CHARINDEX('%Enroll To:%', AssignedTo) + LEN('%Enroll To:%') 
            ,CHARINDEX('Goals', AssignedTo) - CHARINDEX('%Enroll To:%', AssignedTo) + LEN('Goals'))
          ELSE 'None'
          END AS 'Name'
FROM
(

  Select 
CASE WHEN ColumnName like '%Enroll To:%' THEN SUBSTRING    (ColumnName, CHARINDEX('Enroll To:', ColumnName), 40) 


ELSE 'None'
END AS 'AssignedTo'

FROM TABLE ) A

I cannot thank you enough!

Please edit your question and provide real sample data. We can't guess at the answer. — Tim Biegeleisen, Nov 05 '18 at 21:14

inefficientmkts · Accepted Answer · 2018-11-07T19:52:09.153

This produced the desired result and seems to deal with variable length of the target string. Hope it helps someone.

DECLARE @pretext as NVARCHAR(100) = 'Enroll to:' 
DECLARE @posttext as NVARCHAR(100) = 'Goals'

Select 
,CASE When CHARINDEX(@posttext, ColumnName) - (CHARINDEX(@pretext, ColumnName) + len(@pretext)) < 0 THEN NULL
    Else
    SUBSTRING(ColumnName, CHARINDEX(@pretext, ColumnName) + len(@pretext)
    ,CHARINDEX(@posttext, ColumnName) - (CHARINDEX(@pretext, ColumnName) + len(@pretext)) )    
    END as betweentext 

FROM TABLE

score 0 · Answer 2 · answered Nov 05 '18 at 20:27

0

You can use apply and string functions:

select left(v.s1, charindex(' ', s1) - 1)
from t cross apply
     (values (stuff(t.col, 1, 11, '')) v(s1)

answered Nov 05 '18 at 20:27

Gordon Linoff

1,242,037
58
646
786

Should clarify that there are a variable number of characters before and after the fields in 'columnname' – inefficientmkts Nov 05 '18 at 20:51

Tim Biegeleisen · Answer 3 · 2018-11-05T20:54:17.530

0

Here is an alternative to Gordon's answer:

SELECT
    SUBSTRING(ColumnName,
              CHARINDEX(':', ColumnName) + 2,
              CHARINDEX(' ', ColumnName, CHARINDEX(':', ColumnName) + 2) -
                  CHARINDEX(':', ColumnName) - 2) AS Name
FROM yourTable;

Demo

edited Nov 05 '18 at 20:54

answered Nov 05 '18 at 20:34

Tim Biegeleisen

502,043
27
286
360

There's a variable length of data before and after the field information shown – inefficientmkts Nov 05 '18 at 20:44
@inefficientmkts Answer updated. It works now with a variable field width. – Tim Biegeleisen Nov 05 '18 at 20:54
Thank you very much. I tried it and the same error is produced "invalid length parameter passed to the left or substring function' There may be other instances in the field where a : or ' ' exists. Dunno if that helps – inefficientmkts Nov 05 '18 at 21:08
@inefficientmkts Your sample data [is working in this demo](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=648fe0e61446d35bd75ae09fb3d6d4b9). I am asking you for data which exposes the problems in my answer, and you still have not provided that. I can't help you without seeing your data. – Tim Biegeleisen Nov 05 '18 at 21:11
My apologies Tim- I didn't recognize and properly identify some of the variations within the column. I have provided the working data you requested. Thanks again, this really has me stumped. – inefficientmkts Nov 07 '18 at 16:34

score 0 · Answer 4 · answered Nov 07 '18 at 20:12

Here is your data to test for in table form:

declare @goals table (string nvarchar(255));
insert @goals values 
    ('Enroll to: Carol Goals are many and varied  characters that don''t include desired results'),
    ('Enroll to: Jan Levinson Goals will be discussed at first encounter'),
    ('Enroll to: Stephon-Anderson   Goals none'),
    (NULL),
    ('Enroll to:   David   Goals  '), --Note uneven spaces, Need David 
    (' '); -- I (psw) added this

And the following code seems to do what you desire without error. But it is assuming that your sentence after the name will always start with "Goals".

select      *,
            result = 
                case 
                when isValid = 1 then 
                    ltrim(rtrim(
                        substring(string, colonPos + 1, goalsPos - colonPos - 1)
                    ))
                end

from        @goals
cross apply (select 
                colonPos = charindex(':', string),
                goalsPos = patIndex('%goals%', string)
            ) positions
cross apply (select
                isValid = 
                    case 
                    when colonPos = 0 or goalsPos = 0 or colonPos > goalsPos then 0
                    else 1
                    end
            ) validity

How to select a variable length string from between two known strings in SQL Server in a VARCHAR(MAX) where some columns don't have applicable strings

4 Answers4

Demo