Post reply

Substring for Variable length

  • March 9, 2017 at 5:43 am

    #319527

    Hi,

    Need one small help in getting two values from a string value using substring. Here is the scenario


    DECLARE @Source VARCHAR(100)
    Set @Source ='model://CMDTY/SRC_Name'

    so from @Source variable i need to retrieve CMDTY and SRC_Name value separately, but in some case i receive @Source variable as:


    Set @Source ='model://CMDTY/SRC_Name/MID'
    Set @Source ='model://CMDTY/SRC_Name/MID/Deploy'

    so in both cases the value should always be CMDTY and SRC_Name. Please let me know how to use substring for variable length of inputs and to retrieve from specific position only.

    Thanks
    Sam

  • March 9, 2017 at 6:05 am

    #1932484

    Perhaps I am missing something, but the position of both CMDTY and SRV_Name are in the same position in your example, so the following will always work:
    SELECT SUBSTRING(@Source, 9,5) AS CMDTY, SUBSTRING(@Source, 15,8) AS Srv_Name;

    Could you, perhaps, give us some examples showing you actual needs, with varying requirements please?

    Thanks 🙂

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • March 9, 2017 at 6:28 am

    #1932490

    Here CMDTY and SRC_Name are just an example. And it can vary in length.

    For instance for SRC_Name i need to retrieve the value after first occurance of '/' in the variable @source.

    Hope i made it clear. Let me know if there are confusions.

    Here are some real examples of source values:


    'model://MONEY/GBPCHF.3Y.BG0L.125'
    'model://INDEX/IMAREX.PANAMAX'
    'model://INDEX_New/JIBAR.Z2AR.12M1/LAST'
    'model://FXG_LBMA/EU.ME.LBMA.A3G_USD.FXG/PRICE'

    -- Here is the output i needed

    Col1                COL2
    FXG_LBMA     EU.ME.LBMA.A3G_USD.FXG
    INDEX            IMAREX.PANAMAX
    INDEX_New   JIBAR.Z2AR.12M1
    MONEY          GBPCHF.3Y.BG0L.125

  • March 9, 2017 at 7:06 am

    #1932497

    Search this site for "splitter functions".  You can use a splitter function with "/" as the delimiter.

    John

  • March 9, 2017 at 7:18 am

    #1932502

    Using Substring:
    USE DevTestDB;
    GO

    CREATE TABLE #Source (String varchar(255));
    GO

    INSERT INTO #Source
    VALUES
    ('model://MONEY/GBPCHF.3Y.BG0L.125'),
    ('model://INDEX/IMAREX.PANAMAX'),
    ('model://INDEX_New/JIBAR.Z2AR.12M1/LAST'),
    ('model://FXG_LBMA/EU.ME.LBMA.A3G_USD.FXG/PRICE');
    GO

    SELECT *
    FROM #Source;

    SELECT SUBSTRING(String + '/',9, CHARINDEX('/',String,9)-9) AS CMDTY,
           SUBSTRING(String + '/', CHARINDEX('/',String + '/',9) +1, CHARINDEX('/',String + '/',CHARINDEX('/',String + '/',9)+1) - CHARINDEX('/',String + '/',9) - 1) AS [Srv_Name]
    FROM #Source;

    GO

    DROP TABLE #Source;
    GO

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • March 9, 2017 at 10:29 pm

    #1932646

    Thank you so much Thom. Your solution works...

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply