How to extract this specific substring in SQL Server?

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.

  • Service Broker — how to extract the rows from the XML message?
  • How can I use EF to add multiple child entities to an object when the child has an identity key?
  • Error:create function must be the only statement in the batch
  • How to get server name through code if SQL Server (Standard Edition) is installed
  • PHP & SQL Server - field names truncated
  • Need help in bit complicated SQL query
  • 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.

  • Order by distinct but then loop round
  • Cross database querying in EF
  • How to map SQL Server tables to DataTables correctly?
  • What do these Copy Only Backup options mean?
  • Can't have the same table names in different entity framework models?
  • EF6 Application Code
  • 5 Solutions collect form web for “How to extract this specific substring in SQL Server?”

    Combine the SUBSTRING(), LEFT(), and CHARINDEX() functions.

                          CHARINDEX(';', YOUR_FIELD) + 1, 100),
                          CHARINDEX('[', YOUR_FIELD) - 1)

    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.

    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)

    An alternative to the answer provided by @Marc

        CHARINDEX(';', YOUR_FIELD) > 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.

    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 

    Hope this helps.

    select substring(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!

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.