I have two strings in my SQL Server database and I want to get specific values from them in a query

The first string is

www.abc.c/item/itemscode/012345678974-Thisisname

I want to get the only portion that is between / and _ i.e only 012345678974

The second is

abc.org.c/items/item_database/number_3_of_10-klsnfdsfjsd.txt

From this string I want to get only 3 from "number_3_of...."

How can I do it. I tried many solution but i could not do it in past more than 48 hours

1 answer

  • answered 2018-10-11 20:15 Kashif Qureshi

    There are many different ways to achieve this. One way to achieve this is by using Left(), Reverse() and Charindex() function. You need to search for the pattern in your string and base your solution depending on that. Try this:

    --First string     
        DECLARE @string VARCHAR(255)= 'www.abc.c/item/itemscode/012345678974-Thisisname';
        SELECT LEFT(REVERSE(LEFT(REVERSE(@string), CHARINDEX('/', REVERSE(@string))-1)), CHARINDEX('-', REVERSE(LEFT(REVERSE(@string), CHARINDEX('/', REVERSE(@string))-1)))-1);
    
    --Second String     
        DECLARE @string2 VARCHAR(255)= 'abc.org.c/items/item_database/number_3_of_10-klsnfdsfjsd.txt';
        SELECT REPLACE(REPLACE(LEFT(REVERSE(LEFT(REVERSE(@string2), CHARINDEX('/', REVERSE(@string2))-1)), CHARINDEX('-', REVERSE(LEFT(REVERSE(@string2), CHARINDEX('/', REVERSE(@string2))-1)))-1), 'NUMBER_', ''), '_OF_10', '');