CODEWITHSUNDEEP
sql-servert-sqlvarcharmax
inefficientmkts
inefficientmkts

Reputation: 89

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

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!

Upvotes: 0

Views: 1415

Answers (4)

pwilcox
pwilcox

Reputation: 5753

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

Upvotes: 0

inefficientmkts
inefficientmkts

Reputation: 89

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

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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)

Upvotes: 0

Related Questions