How to extract this specific substring in SQL Server

Question

I have a string with a specific pattern   23 chair red   3    i e   a number followed by a semicolon  then a name followed by a left square bracket   Assuming the semicolon   always exists and the left square bracket   always exists in the string  how do I extract the text between  and not including  the   and the   in a SQL Server query  Thanks

User · Answer

If you need to split something into 3 pieces, such as an email address and you don't know the length of the middle part, try this (I just ran this on sqlserver 2012 so I know it works):

SELECT top 2000 
    emailaddr_ as email,
    SUBSTRING(emailaddr_, 1,CHARINDEX('@',emailaddr_) -1) as username,
    SUBSTRING(emailaddr_, CHARINDEX('@',emailaddr_)+1, (LEN(emailaddr_) - charindex('@',emailaddr_) - charindex('.',reverse(emailaddr_)) ))  domain 
FROM 
    emailTable
WHERE 
    charindex('@',emailaddr_)>0 
    AND 
    charindex('.',emailaddr_)>0;
GO

Hope this helps.

User · Answer

An alternative to the answer provided by  Marc  SELECT SUBSTRING LEFT YOUR FIELD  CHARINDEX      YOUR FIELD  - 1   CHARINDEX      YOUR FIELD    1  100  FROM YOUR TABLE WHERE CHARINDEX      YOUR FIELD   gt  0 AND     CHARINDEX      YOUR FIELD   gt  0    This makes sure the delimiters exist  and solves an issue with the currently accepted answer where doing the LEFT last is working with the position of the last delimiter in the original string  rather than the revised substring

User · Answer

Assuming they always exist and are not part of your data  this will work   declare  string varchar 8000     23 chair red   3   select substring  string  charindex       string    1  charindex        string  - charindex       string  - 1

User · Answer

Combine the SUBSTRING    LEFT    and CHARINDEX   functions   SELECT LEFT SUBSTRING YOUR FIELD                        CHARINDEX      YOUR FIELD    1  100                         CHARINDEX      YOUR FIELD  - 1  FROM YOUR TABLE    This assumes your field length will never exceed 100  but you can make it smarter to account for that if necessary by employing the LEN   function   I didn t bother since there s enough going on in there already  and I don t have an instance to test against  so I m just eyeballing my parentheses  etc

User · Answer

select substring your field  CHARINDEX     your field  1  CHARINDEX     your field -CHARINDEX     your field -1   from your table  Can t get the others to work  I believe you just want what is in between     and     in all cases regardless of how long the string in between is  After specifying the field in the substring function  the second argument is the starting location of what you will extract  That is  where the     is   1  fourth position - the c   because you don t want to include      The next argument takes the location of the      position 14  and subtracts the location of the spot after the      fourth position - this is why I now subtract 1 in the query   This basically says substring field location I want substring to begin  how long I want substring to be   I ve used this same function in other cases  If some of the fields don t have     and      you ll want to filter those out in the  where  clause  but that s a little different than the question  If your     was say           you would use 3 instead of 1 in the example  Hope this helps

[sql-server] How to extract this specific substring in SQL Server?

Examples related to sql-server

Examples related to substring