SQL with SUBSTRING / CHARINDEX I've got a VARCHAR variable of this kind:

@string = "BLABLA10;GOGOGO30;RES777;ANOTHER;"


(several keyword + number separated by ;)

I need to get the INT value after "RES" in a T-SQL query (using SYBASE).

I thought it would be always 3 characters, so I did:

SELECT @val = CONVERT(INT, LTRIM(RTRIM(SUBSTRING(@string, CHARINDEX(";RES", @string)+4, 3))))


But now, it can be 2 characters (or more) for example... so I need to get the value between RES and the next ;

I've done a complicated query:

CONVERT(INT, LTRIM(RTRIM(SUBSTRING(@string, CHARINDEX(";RES", @string)+4, -4 + CHARINDEX(";", SUBSTRING(@string, CHARINDEX(";RES", @string)+1, 70) )))))


It works but it's horrible to read.

Has anyone an idea for a more readable way than this?

I know that the real problem is that we should not use value in concatenated string, but well it was done that way and I can't change it.
You've hit the nail on the head there:

I know that the real problem is that we should not use value in concatenated string, but well it was done that way and I can't change it.

So you are in a situation where you have to over-compensate on the database end to counter a bad design decision. Many of us who write SQL regularly in production environments have been there. So let's see what we can do about it.

I have added new lines and indentations to give a better scope of what your string manipulation query is doing:
DECLARE @string VARCHAR(MAX) = 'BLABLA10;GOGOGO30;RES777;ANOTHER;';
SELECT 
    CONVERT(
        INT, 
        LTRIM(RTRIM(
            SUBSTRING(
                @string, 
                CHARINDEX(
                    ';RES', 
                    @string
                )+4, 
                -4 + CHARINDEX(
                    ';', 
                    SUBSTRING(
                        @string, 
                        CHARINDEX(
                            ';RES', 
                            @string
                        )
                        +1, 
                        70
                    ) 
                )
            )
        ))
    )
;

Note: I have converted &quot;s to 's so I could run it on SQL Server.
This is quite complicated. You've obviously spent a good amount of time putting this together so it would work. I'd say, to avoid WTFs from Mr. or Ms. Maintainer, you should document it.

Inspired by RegEx verbose
There's a regular expression mode in some programming languages that lets you document your regular expression. Since your SQL does similar things than RegEx does, I figured maybe we could find inspiration there. For example sake, this is a documented verbose RegEx from the Python documentation:

a = re.compile(r&quot;&quot;&quot;\d +  # the integral part
                   \.    # the decimal point
                   \d *  # some fractional digits&quot;&quot;&quot;, re.X)


Let's try and do something similar here, starting with the innermost manipulation:
SUBSTRING(  -- get keyword RES and the chars that follow (up to 70 chars max)
    @string, 
    CHARINDEX(      -- find index of 
        ';RES',     -- semicolon just before keyword RES
        @string     -- in @string
    )
    +1,         -- +1 to advance past the semicolon
    70          -- length of up to 70

So it starts making a bit of sense for the next person. Let's keep working our way up the chain from there:
DECLARE @string VARCHAR(MAX) = 'BLABLA10;GOGOGO30;RES777;ANOTHER;';
SELECT 
    /*
    The purpose of this manipulation is to find 
    get the value between RES and the next ;
    in a string of keywords and numbers. (referred to as @string)
    NOTE: Read function documentation from inner to outer to understand how this works.
    */
    CONVERT(
        INT,    -- returned value needs to be an integer
        LTRIM(RTRIM(    -- trim potential white space before or after the value
            SUBSTRING(      -- String of characters between end of RES and following semicolon
                @string,        -- in @string
                CHARINDEX(      -- find index of
                    ';RES',     -- keyword RES and semicolon just before it
                    @string     -- in @string
                )+4,        -- add 4 chars to move index just past RES keyword
                -4 + CHARINDEX( -- find index of
                    ';',        -- semicolon after the value belonging with RES
                    SUBSTRING(      -- String of keyword RES and the chars that follow (up to 70 chars max)
                        @string, 
                        CHARINDEX(      -- find index of 
                            ';RES',     -- semicolon just before keyword RES
                            @string     -- in @string
                        )
                        +1,         -- +1 to advance past the semicolon
                        70          -- length of up to 70 chars
                    ) 
                )
            )
        ))
    )
;

Looking at it now, there are already a few things that stand out:

Length 70?
Do you really need to capture RES and up to 70 characters? You are converting the string to a signed 4-byte int which will at most be 2,147,483,647 (10 digits). So, just to be safe, let's factor in the keyword RES, and the following ;, and one extra for good luck. So let's change 70 to 15.
SUBSTRING(@string, CHARINDEX(&quot;;RES&quot;, @string)+1, 15) )

If you needed to change the outermost CONVERT to say, a BIGINT, then you would need a longer string, but that seems not to be the case based on your code.

LTRIM(RTRIM(
I know that SQL operators often include this &quot;just in case&quot;, so I don'tblame you for doing so. But since you're seeking a review, I will point out that perhaps you should think of what are the chances of this actually happening?
To illustrate, a use case of this happening would look perhaps like this:

@string = &quot;BLABLA10;GOGOGO30;RES   777  ;ANOTHER;&quot;


If that is not possible, then you may as well just remove the LTRIM(RTRIM( part altogether. In fact, it looks like converting to int already does that for you, so just remove it.
Demo:

SELECT CONVERT(INT, '   777  ')
-- Result: 777


Note: If you do expect that empty spaces will be present, you may need to adjust the 15 value above (instead of 70) to a higher number to account for this.

Speaking of RegEx...
Did you consider using Regular Expressions instead of this complicated manipulation? Here are a few links for you to consider:

Create a UDF to support Regular Expressions in T-SQL

StackOverflow: Regex Match all characters between two strings


If you supported RegEx it may be as simple as just search for (?&lt;=RES)(.*)(?=;) instead of doing this whole mammoth maneuver.