A SQL Query to select a string between two known strings

Question

I need a SQL query to get the value between two known strings  the returned value should start and end with these two strings    An example    All I knew was that the dog had been very bad and required harsh punishment immediately regardless of what anyone else thought    In this case the known strings are  the dog  and  immediately   So my query should return  the dog had been very bad and required harsh punishment immediately   I ve come up with this so far but to no avail   SELECT SUBSTRING  Text  CHARINDEX  the dog    Text   CHARINDEX  immediately    Text      Text being the variable containing the main string   Can someone please help me with where I m going wrong

User · Accepted Answer

The problem is that the second part of your substring argument is including the first index  You need to subtract the first index from your second index to make this work   SELECT SUBSTRING  Text  CHARINDEX  the dog    Text    CHARINDEX  immediately   text  - CHARINDEX  the dog    Text    Len  immediately

User · Answer

Try this and replace      amp      with your string  SELECT SUBSTRING  TEXT CHARINDEX      TEXT  1  CHARINDEX      TEXT -CHARINDEX      TEXT  -1

User · Answer

I had a similar need to parse out a set of parameters stored within an IIS logs  csUriQuery field  which looked like this  id 3598308 amp user AD user amp parameter 1 amp listing No needed in this format   I ended up creating a User-defined function to accomplish a string between  with the following assumptions    If the starting occurrence is not found  a NULL is returned  and  If the ending occurrence is not found  the rest of the string is returned   Here s the code   CREATE FUNCTION dbo str between  col varchar max    start varchar 50    end varchar 50       RETURNS varchar max      WITH EXECUTE AS CALLER   AS   BEGIN     RETURN substring  col  charindex  start   col    len  start             isnull nullif charindex  end  stuff  col  1  charindex  start   col -1       0            len stuff  col  1  charindex  start   col -1       1  - len  start -1   END    GO   For the above question  the usage is as follows   DECLARE  a VARCHAR MAX     All I knew was that the dog had been very bad and required harsh punishment immediately regardless of what anyone else thought   SELECT dbo str between  a   the dog    immediately   -- Yields  had been very bad and required harsh punishment

User · Answer

SELECT  SUBSTRING   123 yahoo com    charindex      123 yahoo com  1    1  charindex      123 yahoo com  1  - charindex      123 yahoo com  1  - 1

User · Answer

An example is this  You have a string and the character    String     aaaaa bbbbb ccccc   Code    SELECT SUBSTRING  aaaaa bbbbb ccccc  CHARINDEX      aaaaa bbbbb ccccc   1  CHARINDEX      aaaaa bbbbb ccccc  CHARINDEX      aaaaa bbbbb ccccc   1  -CHARINDEX      aaaaa bbbbb ccccc  -1  as My String   Output    bbbbb

User · Answer

You re getting the starting position of  punishment immediately   but passing that in as the length parameter for your substring   You would need to substract the starting position of  the dog  from the charindex of  punishment immediately   and then add the length of the  punishment immediately  string to your third parameter  This would then give you the correct text   Here s some rough  hacky code to illustrate the process   DECLARE  text VARCHAR MAX  SET  text    All I knew was that the dog had been very bad and required harsh punishment immediately regardless of what anyone else thought    DECLARE  start INT SELECT  start   CHARINDEX  the dog   text   DECLARE  endLen INT SELECT  endLen   LEN  immediately    DECLARE  end INT SELECT  end   CHARINDEX  immediately   text  SET  end    end -  start    endLen  SELECT  end  SELECT SUBSTRING  text  start  end    Result  the dog had been very bad and required harsh punishment immediately

User · Answer

DECLARE  Text VARCHAR MAX    First VARCHAR MAX    Second VARCHAR MAX  SET  Text    All I knew was that the dog had been very bad and required harsh punishment immediately regardless of what anyone else thought   SET  First    the dog  SET  Second    immediately   SELECT SUBSTRING  Text  CHARINDEX  First   Text                     CHARINDEX  Second   Text  - CHARINDEX  First   Text    LEN  Second

User · Answer

I think what Evan meant was this    SELECT SUBSTRING  Text  CHARINDEX  First   Text    LEN  First                     CHARINDEX  Second   Text  - CHARINDEX  First   Text  - LEN  First

User · Answer

SELECT SUBSTRING  aaaaa bbbbb ccccc  instr  aaaaa bbbbb ccccc      1 1  1  instr  aaaaa bbbbb ccccc      1 2 -1  -instr  aaaaa bbbbb ccccc      1 1   as My String

User · Answer

select substring  string charindex   first   string  1 charindex   second   string - charindex   first   string  1

User · Answer

lt pre gt    DECLARE  text VARCHAR MAX   SET  text    All I knew was that the dog had been very bad and required harsh punishment immediately regardless of what anyone else thought    declare  pretext as nvarchar 100     the dog      declare  posttext as nvarchar 100     immediately   SELECT   CASE  When CHARINDEX  posttext   Text  -  CHARINDEX  pretext   Text    len  pretext    lt  0 THEN     Else SUBSTRING  Text  CHARINDEX  pretext   Text    len  pretext      CHARINDEX  posttext   Text  -  CHARINDEX  pretext   Text    len  pretext         END as betweentext

User · Answer

I have a feeling you might need SQL Server s PATINDEX   function  Check this out   Usage on Patindex   function  So maybe   SELECT SUBSTRING  TEXT  PATINDEX   the dog     TEXT   PATINDEX   immediately    TEXT

User · Answer

Hope this helps   Declared a variable   in case of any changes need to be made thats only once   declare  line  varchar 100   set  line   Email i-Julie mail com   select SUBSTRING  line   charindex  -   line  1   CHARINDEX      line -charindex  -   line -1

User · Answer

You need to adjust for the LENGTH in the SUBSTRING   You were pointing it to the END of the  ending string    Try something like this   declare  TEXT varchar 200  declare  ST varchar 200  declare  EN varchar 200  set  ST    the dog  set  EN    immediately  set  TEXT    All I knew was that the dog had been very bad and required harsh punishment immediately regardless of what anyone else thought   SELECT SUBSTRING  Text  CHARINDEX  ST   Text    CHARINDEX  EN   Text  LEN  EN  -CHARINDEX  ST   Text     Of course  you may need to adjust it a bit

[sql] A SQL Query to select a string between two known strings

Examples related to sql

Examples related to sql-server

Examples related to substring