Using substring with multiple identical characters

Question

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.

Do you want the filename (minus extension)? This question has a clever way of implementing "last char index": stackoverflow.com/questions/39002025/… — charles-allen
– charles-allen, Commented May 21, 2019 at 8: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
– Joep_S, Commented May 21, 2019 at 8:38
Is it always the text between the last / and the last . ? — charles-allen
– charles-allen, Commented May 21, 2019 at 8:41
@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
– Panagiotis Kanavos, Commented May 21, 2019 at 8:45
@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
– Panagiotis Kanavos, Commented May 21, 2019 at 9:02

Bart Hofland · Accepted Answer · 2019-05-21 09:13:45Z

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.

edited May 21, 2019 at 9:13

answered May 21, 2019 at 9:01

Bart Hofland

3,9251 gold badge18 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Panagiotis Kanavos Over a year ago

The OP is looking for line feeds, not dots or slashes

Bart Hofland Over a year ago

I saw and I was already editing it. Thanks for the remark though.

Joep_S Over a year ago

Thanks Bart! Seems to be working after some preliminary testing. BTW, multiple 'my specific string's are not possible and there should always be a char(10) after the string so it should work for all records in the database.

Collectives™ on Stack Overflow

Using substring with multiple identical characters

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related