0

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.

Joep_S
  • 439
  • 3
  • 12
  • Do you want the filename (minus extension)? This question has a clever way of implementing "last char index": https://stackoverflow.com/questions/39002025/is-there-a-lastindexof-in-sql-server – charles-allen May 21 '19 at 08:35
  • No, I just used the example from the referenced website. In my case it's all kind of free text fields in the database. So basically, I want all text between the / and the first . that follows the / – Joep_S May 21 '19 at 08:38
  • Is it always the text between the last `/` and the last `.` ? – charles-allen May 21 '19 at 08:41
  • No, in my case the / is a specific string and the . is a char(10). I would like all text after the specific string, but prior to the next 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. – Joep_S May 21 '19 at 08:45
  • 1
    @Joep_S what is the actual problem? You're describing your attempted solution, not the actual problem. This makes it really hard to understand what you're trying to do, especially since there are no input/output examples. SQL, the language, is bad at string manipulation anyway. – Panagiotis Kanavos May 21 '19 at 08:45
  • @Joep_S the `.` character is not `CHAR(10)` and using a different problem that's "similar" says nothing about what *you* are trying to do. The linked question is probably not relevant to your problem at all - that question asked about URLs while you ask about *newlines* – Panagiotis Kanavos May 21 '19 at 08:48
  • @Joep_S are you trying to split lines? You can use `STRING_SPLIT` in SQL Server 2016 and later. If you want a *specific* line you could convert the string to JSON or XML with a few replacements and access the line you want by index. Or you could use a SQLCLR function to easily manipulate the string using eg a Regex or the methods in the `Uri` or `Path` classes – Panagiotis Kanavos May 21 '19 at 08:49
  • @Panagiotis, I rephrased my question. I hope this makes it clearer – Joep_S May 21 '19 at 08:59
  • 1
    @Joep_S the question needs rewriting, not rephrasing. There's no example of inputs or outputs. `CHAR(10)` is a *line feed*, not just any character which means you're trying to manipulate multiline text. That's completely different from code that handles URIs or paths. A *good* question would be `How can I read the second line in a multiline string?` or `How can I read the rest of the line in a multiline string` ? – Panagiotis Kanavos May 21 '19 at 09:02
  • The question is clear to me. It is only not clear if the question covers the actual requirements. But that is up to the OP. – Bart Hofland May 21 '19 at 09:10
  • The rephrased question makes sense to me and I think the Bart's answer solves it. You just need to use the index of `your specific string` as the start index for finding the line break. – charles-allen May 21 '19 at 09:30

1 Answers1

3

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.

Bart Hofland
  • 3,158
  • 1
  • 9
  • 20