CODEWITHSUNDEEP
sql-serversubstring
Joep_S
Joep_S

Reputation: 537

Using substring with multiple identical characters

I want to use substring in SQL server to capture a string between a specific text string and the following char(10). The problem is that there are several occurrences of char(10) in the complete string so I need some code to localize the first char(10) after my specific string. Using the code below results in an error due to a negative value (first char(10) occurs prior to the my specific string).

SELECT SUBSTRING(col, LEN(LEFT(col, CHARINDEX ('my specific string', col))) + 1, LEN(col) - LEN(LEFT(col, 
    CHARINDEX ('my specific string', col))) - LEN(RIGHT(col, LEN(col) - CHARINDEX (char(10), col))) - 1);

Error: Invalid length parameter passed to the LEFT or SUBSTRING function.

Upvotes: 0

Views: 528

Answers (1)

Bart Hofland
Bart Hofland

Reputation: 3905

A very straightforward answer to the specific data in this question: you could pass a third parameter to the CHARINDEX function call that looks for the CHAR(10) after a specific starting index. As the third parameter, you can pass the index of the 'my specific string' you found:

SELECT SUBSTRING(col, LEN(LEFT(col, CHARINDEX ('my specific string', col))) + 1, LEN(col) - LEN(LEFT(col, 
    CHARINDEX ('my specific string', col))) - LEN(RIGHT(col, LEN(col) - CHARINDEX (char(10), col, CHARINDEX ('my specific string', col)))) - 1);

I am not sure if this will actually solve your final issue, however. Perhaps your col could not contain a CHAR(10) after a 'my specific string'... Or it might contain multiple 'my specific string's as well. If you want all logic that handles your (more complex) requirements in a single SELECT-statement, things can get messy very quickly.

Upvotes: 3

Related Questions