How to Select Middle String from Text in SQL Server?

How to Select Middle String from Text in SQL Server?

Before going to explanation kindly know 

two functions PATINDEX & CHARINDEX

Ref:
https://msdn.microsoft.com/en-IN/library/ms188395.aspx

SYNTAX:
 

PATINDEX ( '%pattern%' , expression ) 

If either pattern or expression is NULL, PATINDEX returns NULL. 

PATINDEX 

 Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.

SELECT PATINDEX('%ter%', 'interesting data');  

Here is the result set.
3

Using wildcard characters with PATINDEX

The following example uses % and _ wildcards to find the position at which the pattern 'en', followed by any one character and 'ure' starts in the specified string (index starts at 1):

SELECT PATINDEX('%en_ure%', 'please ensure the door is locked');  
  

Here is the result set.
------------

8 

PATINDEX works just like LIKE, so you can use any of the wildcards. You do not have to enclose the pattern between percents. PATINDEX('a%', 'abc') returns 1 and PATINDEX('%a', 'cba') returns 3.
Unlike LIKE, PATINDEX returns a position, similar to what CHARINDEX does.

Using wildcard characters with PATINDEX

The following example uses wildcard characters to find the position at which the pattern whe_l starts in the EnglishDescription column in the dbo.DimProduct table, where the underscore is a wildcard representing any character. If you do not restrict the rows to be searched, the query returns all rows in the table and reports nonzero values for those rows in which the pattern was found.

-- Uses AdventureWorks  
  
SELECT ProductKey,   
    PATINDEX('%whe_l%',EnglishDescription)AS StartingPosition,  
EnglishDescription  
FROM dbo.DimProduct  
WHERE EnglishDescription LIKE '%wheel%'  
ORDER BY ProductKey; 

CHARINDEX:

Searches an expression for another expression and returns its starting position if found.

Syntax

CHARINDEX ( expressionToFind ,expressionToSearch [ , start_location ] ) 

Returning the starting position of an expression

The following example returns the position at which the sequence of characters bicycle starts in the DocumentSummary column of the Document table in the AdventureWorks2012 database.

DECLARE @document varchar(64);  
SELECT @document = 'Reflectors are vital safety' +  
                   ' components of your bicycle.';  
SELECT CHARINDEX('bicycle', @document);  
GO  

Here is the result set.

-----------   
48            

B. Searching from a specific position

The following example uses the optional start_location parameter to start looking for vital at the fifth character of the DocumentSummary column in the AdventureWorks2012 database.

DECLARE @document varchar(64);  
  
SELECT @document = 'Reflectors are vital safety' +  
                   ' components of your bicycle.';  
SELECT CHARINDEX('vital', @document, 5);  
GO  

Here is the result set.

-----------   
16            
  
(1 row(s) affected)  

Declare @OrdTest Table(Note Varchar(Max))

    Insert into @OrdTest
    SELECT 'daasd ORD2343 fksdkfhldkh'
    UNION ALL
    SELECT 'dasdsad ORDERNEW sdsaldjalsdja'
    UNION ALL
    SELECT 'there is ORD4679 sdsaldjalsdja'
    UNION ALL
    SELECT 'ODER Ssf sf ORDER124679 sdsaldjalsdja'


-----SUBSTRING ( expression ,start , length )
 SELECT SUBSTRING(Note, PATINDEX ('%ORD%', Note),
 (CHARINDEX ( ' ', Note,PATINDEX ('%ORD%', Note)) - CHARINDEX ( ' ', Note,PATINDEX ('% ORD%', Note)))) [Result]
 FROM @OrdTest

 Explanation:

DECLARE @WORD    VARCHAR(1000)
SET @WORD ='It is 487 ORDINFO HEARD SOME'
                  -->10      18------------------->position
                      
SELECT PATINDEX ('%ORD%', @WORD) [ORD start point]

SELECT CHARINDEX ( ' ', @WORD,PATINDEX ('%ORD%', @WORD))[Result]------>it gives from 'ORD' ORD starting point 11 what is the space After position here 18

SELECT CHARINDEX ( ' ', @WORD,PATINDEX ('% ORD%', @WORD))[Result]------>it gives from' ORD' Space ORD starting point 11 what is the space before position here 10

 SELECT (CHARINDEX ( ' ', @WORD,PATINDEX ('%ORD%', @WORD)) - CHARINDEX ( ' ', @WORD,PATINDEX ('% ORD%', @WORD)))[Result] --->WE Need to subtract to get ORD with complete word