How to Extract Specific Text Fragments in SQL Server Using SUBSTRING and CHARINDEX?

Retrieving Specific Text Fragments in SQL Server

SQL Server provides versatile functions for extracting specific portions of text strings. This question pertains to isolating a text segment from a longer entry by targeting characters before and after a predefined delimiter.

To accomplish this specific requirement, you can employ the SUBSTRING function. The syntax for SUBSTRING is:

SUBSTRING(<expression>, <start>, <length>)

• specifies the text string from which the substring will be extracted.
• indicates the character position within the where the substring extraction begins (1-based index).
• specifies the number of characters to extract from the start position.

Retrieving Text After a Delimiter

To extract the portion of text after a specific character, such as a slash (/), you can use the CHARINDEX function. This function returns the first occurrence of a specified substring within a text string.

The code to retrieve text after a slash would be:

SELECT SUBSTRING(@text, CHARINDEX('/', @text) + 1, LEN(@text))

For example, if the input text is "images/test.jpg", the output will be "test.jpg".

Retrieving Text Before a Delimiter

To extract the portion of text before a specific character, such as a dot (.), you can use a similar approach:

SELECT SUBSTRING(@text, 1, CHARINDEX('.', @text) - 1)

For the same input string, the output will be "images/test".

The above is the detailed content of How to Extract Specific Text Fragments in SQL Server Using SUBSTRING and CHARINDEX?. For more information, please follow other related articles on the PHP Chinese website!