SQL Server Performance

Substring logic

Discussion in 'General Developer Questions' started by SanetteWessels, May 10, 2004.

  1. SanetteWessels New Member

    Hi everyone,

    I need help on the following. I have a string: 'tbl_Forecast_123456_789010' and I need to extract the number fields from the string.

    So my result should look like:

    Num1 = 123456, Num2 = 789010

    Thanks in advance!!


    SQL Developer
    JustEnough Software Corporation
    Walking on water and coding of a spec is easy, as long as both are frozen - A friend
  2. Luis Martin Moderator

    If string is fix length, you can use:

    SUBSTRING ( expression , start , length )



    Luis Martin
    Moderator
    SQL-Server-Performance.com

  3. satya Moderator

  4. SanetteWessels New Member

    Hi everyone.

    Thanks for the advice. I managed to write the following code and thought I would share it with you.

    DECLARE @len int
    , @string varchar(50)
    , @TableName varchar(50)

    SET @TableName = 'tbl_Forecast_23344556_33223223'

    SELECT @len = LEN(REVERSE(LEFT(REVERSE(@TableName),CHARINDEX('_',REVERSE(@TableName))-1)))+1
    SELECT @len = @len + 1

    SELECT @string = REVERSE(SUBSTRING(REVERSE(@TableName),@len,50))

    SELECT ID1 = REVERSE(LEFT(REVERSE(@string),CHARINDEX('_',REVERSE(@string))-1))
    ,ID2 = REVERSE(LEFT(REVERSE(@TableName),CHARINDEX('_',REVERSE(@TableName))-1))


    SQL Developer
    JustEnough Software Corporation
    Walking on water and coding of a spec is easy, as long as both are frozen - A friend
  5. gaurav_bindlish New Member

    That's a lot of functions.... Try using RIGHT for the same....

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

Share This Page