Oracle REGEXP_SUBSTR to SQL Server?

No regex (and no uppercase and lowercase)

To select the text between CATEGORY= and the first , that follows (or until the end of the text):

CASE WHEN CHARINDEX('CATEGORY=',m.DESCRIPTION) > 0
          THEN SUBSTRING(m.DESCRIPTION,
                         CHARINDEX('CATEGORY=',m.DESCRIPTION) + LEN('CATEGORY='),
                         CHARINDEX(',',m.DESCRIPTION+',',CHARINDEX('CATEGORY=',m.DESCRIPTION)+1) - CHARINDEX('CATEGORY=',m.DESCRIPTION) - LEN('CATEGORY=')
                        )
     ELSE ''
END

Example:

+----+-----------------------------------------------+-------------+
|    |                  DESCRIPTION                  |  Resultado  |
+----+-----------------------------------------------+-------------+
|  1 | ID=123444,CATEGORY=ABC,OTRO=####              | ABC         |
|  2 | ID=123444,CATEGORY=ABCDEF,OTRO=####,MAS=GHIJK | ABCDEF      |
|  3 | ID=123444,CATEGORY=A B C D E F,OTRO=####      | A B C D E F |
|  4 | ID=123444,CATEGORY=,OTRO=####                 |             |
|  5 | ID=123444,CATEGORY=ABC                        | ABC         |
|  6 | ID=123444,CATEGORY=ABCDEF                     | ABCDEF      |
|  7 | ID=123444,CATEGORY=                           |             |
|  8 | CATEGORY=ABC,OTRO=####                        | ABC         |
|  9 | CATEGORY=                                     |             |
| 10 | ID=123444,OTRO=####                           |             |
| 11 | ABCDEF                                        |             |
+----+-----------------------------------------------+-------------+

Example on rextester.com

That only matches the uppercase text

If you also want to match only the text with case sensitivity, it depends on which collation ( COLLATION ) is being used in the database.

• The "CS" at the end means that it is case sensitive ( Case Sensitive ), as opposed to "CI" ( Case Insensitive ).

Alternatively, you can change it only for a query using COLLATE , such as Latin1_General_CS_AS .

• Any other can be used as long as the variant "CS" is used, or failing that "bin".

Sentence:

CASE WHEN CHARINDEX('CATEGORY=',m.DESCRIPTION COLLATE Latin1_General_CS_AS) > 0
          THEN SUBSTRING(m.DESCRIPTION,
                         CHARINDEX('CATEGORY=',m.DESCRIPTION COLLATE Latin1_General_CS_AS) + LEN('CATEGORY='),
                         CHARINDEX(',',m.DESCRIPTION+',',CHARINDEX('CATEGORY=',m.DESCRIPTION COLLATE Latin1_General_CS_AS)+1)
                            - CHARINDEX('CATEGORY=',m.DESCRIPTION COLLATE Latin1_General_CS_AS) 
                            - LEN('CATEGORY=')
                        )
     ELSE ''
END

Example on rextester.com

To use regex

If, instead, you are looking to use more complex expressions, in order to use regex in SQL Server, you should use a user-defined function with CLR integration ( Common Language Runtime ).

The following article shows how to use the CLR integration:

• Using CLR Integration in SQL Server 2005

And the following article shows an example to integrate regex in SQL Server:

• Working with Regular Expressions