How can I limit the substring to extract text between two spaces in SQL Server?

Question

Column

2017-03-09 Today ABC.XYZ Work_In_Progress

Output should be:

ABC.XYZ

I can pull from second space but can not limit it till third space:

SELECT ID, SUBSTRING(HISTORY, CHARINDEX(' ', HISTORY, CHARINDEX(' ', HISTORY) +1)+1,LEN(HISTORY)) 
from Test;

You find [a similar question here](https://stackoverflow.com/q/19449492/5089204) — Shnugo, Jun 22 '17 at 15:56

John Cappelletti · Accepted Answer · 2017-06-22T15:39:27.853

5

Yet another option if you know what "position" you are going for. In this case the 3rd string

Declare @YourTable table (ID int,SomeColumn varchar(max))
Insert Into @YourTable values
(1,'2017-03-09 Today ABC.XYZ Work_In_Progress')


Select ID
      ,SomeValue = Cast('<x>' + replace(SomeColumn,' ','</x><x>')+'</x>' as xml).value('/x[3]','varchar(max)')
 From @YourTable

Returns

ID  SomeValue
1   ABC.XYZ

The XML Safe Version

Select ID
      ,SomeValue = Cast('<x>' + replace((Select replace(SomeColumn,' ','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.').value('/x[3]','varchar(max)')
 From  @YourTable

edited Jun 22 '17 at 15:39

answered Jun 22 '17 at 15:27

John Cappelletti

79,615
7
44
66

Unless the data has any xml illegal characters ;) Still gets my vote for simplicity – Pரதீப் Jun 22 '17 at 15:34
@Prdp Thanks - I'll add the XML Safe Option in a moment – John Cappelletti Jun 22 '17 at 15:36
Nice work John! this covers all bases. I wouldn't have thought of that. – Fuzzy Jun 22 '17 at 15:36
@KamranFarzami Thanks.. Yet another reason why I love SO. You get options, and you get pick the one that works best for you. – John Cappelletti Jun 22 '17 at 15:40
You are fishing in my pond :-D (+1 from my side) – Shnugo Jun 22 '17 at 15:52
@Shnugo Can't apologize. It was your Thor'ish answers that started me thinking about XML options. – John Cappelletti Jun 22 '17 at 16:00
np, Great answer :-D All my fish are your fish :-D (I go to polish my X-MLjolnir) – Shnugo Jun 22 '17 at 16:03

score 3 · Answer 2 · answered Jun 22 '17 at 15:22

Here is one way using Charindex and Substring.

DECLARE @str VARCHAR(100) = '2017-03-09 Today ABC.XYZ Work_In_Progress' 

SELECT Substring(string, scd+1, thrd - scd) AS Result 
FROM   (SELECT @str                 string, 
               Charindex(' ', @str) AS fst) A 
       CROSS apply(VALUES (Charindex(' ', string, fst + 1))) cs (scd) 
       CROSS apply(VALUES (Charindex(' ', string, scd + 1))) cs1 (thrd)

score 2 · Answer 3 · answered Jun 22 '17 at 15:31

If you grab a copy of NGrams8K you can create this function called SubstringBetween8K:

CREATE FUNCTION dbo.substringBetween8K
(
  @string    varchar(8000),
  @start     tinyint,
  @stop      tinyint,
  @delimiter char(1)
)
/*****************************************************************************************
Purpose:
 Takes in input string (@string) and returns the text between two instances of a delimiter
 (@delimiter); the location of the delimiters is defined by @start and @stop. 

 For example: if @string = 'xx.yy.zz.abc', @start=1, @stop=3, and @delimiter = '.' the
 function will return the text: yy.zz; this is the text between the first and third
 instance of "." in the string "xx.yy.zz.abc". 

Compatibility: 
 SQL Server 2008+

Syntax:
--===== Autonomous use
 SELECT sb.token, sb.position, sb.tokenLength
 FROM dbo.substringBetween8K(@string, @start, @stop, @delimiter); sb;

--===== Use against a table
 SELECT sb.token, sb.position, sb.tokenLength
 FROM SomeTable st
 CROSS APPLY dbo.substringBetween8K(st.SomeColumn1, 1, 2, '.') sb;

Parameters:
 @string    = varchar(8000); Input string to parse
 @delimiter = char(1); this is the delimiter use to determine where the output starts/ends
 @start     = tinyint; the first instance of @delimiter to search for; this is where the 
              output should start. When @start is 0 then the function will return 
              everything from the beginning of @string until @end. 
 @stop      = tinyint; the last instance of @delimiter to search for; this is where the 
              output should end. When @end is 0 then the function will return everything
           from @start until the end of the string. 

Return Types:
 Inline Table Valued Function returns:
 token       = varchar(8000); the substring between the two instances of @delimiter defined by 
               @start and @stop
 position    = smallint; the location of where the substring begins
 tokenlength = length of the return token

---------------------------------------------------------------------------------------
Developer Notes:
 1. Requires NGrams8K. The code for NGrams8K can be found here:
    http://www.sqlservercentral.com/articles/Tally+Table/142316/

 2. This function is what is referred to as an "inline" scalar UDF." Technically it's an 
    inline table valued function (iTVF) but performs the same task as a scalar valued user
    defined function (UDF); the difference is that it requires the APPLY table operator 
    to accept column values as a parameter. For more about "inline" scalar UDFs see this
    article by SQL MVP Jeff Moden: http://www.sqlservercentral.com/articles/T-SQL/91724/ 
    and for more about how to use APPLY see the this article by SQL MVP Paul White:
    http://www.sqlservercentral.com/articles/APPLY/69953/.

    Note the above syntax example and usage examples below to better understand how to
    use the function. Although the function is slightly more complicated to use than a
    scalar UDF it will yield notably better performance for many reasons. For example, 
    unlike a scalar UDFs or multi-line table valued functions, the inline scalar UDF does
    not restrict the query optimizer's ability generate a parallel query execution plan.

 3. dbo.substringBetween8K is deterministic; for more about deterministic and 
    nondeterministic functions see https://msdn.microsoft.com/en-us/library/ms178091.aspx

Examples:
DECLARE @string  varchar(8000) = '123.ABC456.333.222.3333XXX.$$$'

-- beginning of string to 2nd delimiter, 2nd delimiter to end of the string
SELECT '0, 2',   * FROM dbo.substringBetween8K(@string,0,2, '.')   UNION ALL
SELECT '2, 0',   * FROM dbo.substringBetween8K(@string,2,0, '.')   UNION ALL

-- Between the 1st & 2nd, then 2nd & 5th delimiters
SELECT '1, 2',   * FROM dbo.substringBetween8K(@string,1,2, '.')   UNION ALL
SELECT '2, 5',   * FROM dbo.substringBetween8K(@string,2,5, '.')   UNION ALL

-- dealing with NULLS, delimiters that don't exist and when @first = @last
SELECT '2, 10',  * FROM dbo.substringBetween8K(@string,2,10,'.')   UNION ALL
SELECT '1, NULL',* FROM dbo.substringBetween8K(@string,1,NULL,'.') UNION ALL
SELECT '1, 1',   * FROM dbo.substringBetween8K(@string,1,NULL,'.');

---------------------------------------------------------------------------------------
Revision History: 
 Rev 00 - 20160720 - Initial Creation - Alan Burstein
****************************************************************************************/

RETURNS TABLE WITH SCHEMABINDING AS RETURN
WITH 
chars AS
( 
  SELECT instance = 0, position = 0 WHERE @start = 0
  UNION ALL
  SELECT ROW_NUMBER() OVER (ORDER BY position), position 
  FROM dbo.NGrams8k(@string,1)
  WHERE token = @delimiter
  UNION ALL
  SELECT -1, DATALENGTH(@string)+1 WHERE @stop = 0
)
SELECT token =
         SUBSTRING
         (
           @string, 
           MIN(position)+1, 
           NULLIF(MAX(position),MIN(position)) - MIN(position)-1
         ),
       position = CAST(
         CASE WHEN NULLIF(MAX(position),MIN(position)) - MIN(position)-1 > 0 
         THEN MIN(position)+1 END AS smallint),
       tokenLength = CAST(NULLIF(MAX(position),MIN(position)) - MIN(position)-1 AS smallint)
FROM chars
WHERE instance IN (@start, NULLIF(@stop,0), -1);

To get the text between the 3rd and 4th space you would do this:

DECLARE @txt varchar(100) = '2017-03-09 Today ABC.XYZ Work_In_Progress';

SELECT token
FROM dbo.substringBetween8K(@txt, 2, 3, ' ');

Returns: ABC.XYZ

To use against a table you would do this:

DECLARE @table TABLE(txt varchar(100));

INSERT @table VALUES ('2017-03-09 Today ABC.XYZ Work_In_Progress'), 
('2011-05-09 Today 123.999 Work_NotIn_Progress');

SELECT txt, token
FROM @table
CROSS APPLY dbo.substringBetween8K(txt, 2, 3, ' ');

Returns:

txt                                                token
-------------------------------------------------- -------
2017-03-09 Today ABC.XYZ Work_In_Progress          ABC.XYZ
2011-05-09 Today 123.999 Work_NotIn_Progress       123.999

score 1 · Answer 4 · answered Jun 22 '17 at 15:21

1

You can use LEFT and the logic you already created easily.

DECLARE @HISTORY VARCHAR(50) = '2017-03-09 Today ABC.XYZ Work_In_Progress'

SELECT LEFT(SUBSTRING(@HISTORY, CHARINDEX(' ', @HISTORY, CHARINDEX(' ', @HISTORY) +1)+1,LEN(@HISTORY)) 
    , CHARINDEX(' ' , SUBSTRING(@HISTORY, CHARINDEX(' ', @HISTORY, CHARINDEX(' ', @HISTORY) +1)+1,LEN(@HISTORY))) - 1)

answered Jun 22 '17 at 15:21

Sean Lange

33,028
3
25
40

Thanks @Sean Lange. Works fine. If I don't want to output certain words from the same column how should I modify this query? Let's say the column has JHF word which should be excluded. – django-unchained Jun 22 '17 at 15:37
I don't understand your question about JHF. – Sean Lange Jun 22 '17 at 15:44
Please see below images – django-unchained Jun 22 '17 at 17:38
Add a where clause? Where CHARINDEX(' ', YourColumn) > 0 – Sean Lange Jun 22 '17 at 18:41

How can I limit the substring to extract text between two spaces in SQL Server?

4 Answers4