Post reply

Select Command while dropping first 2 characters in the column

  • March 24, 2009 at 10:19 am

    #202189

    I am looking for a solution to a problem. I can't quite find the command I am looking for. I want to select from a column where the items in the column are like

    ks105

    ks103

    ks106

    and so on

    I want my results to produce the

    105

    103

    106

    and so on.

    I want my select command to drop the first 2 characters in that column when displaying the results. I do not want to modify the information in that table just want my select to display differently.

    Thanks in advance

    Richtoss

  • March 24, 2009 at 10:23 am

    #965378

    Lookup the RIGHT function in Books Online. To get the results you want, you may need to combine it with LEN to get all the string except the first two characters.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 24, 2009 at 11:46 am

    #965462

    You can use the STUFF or SUBSTRING functions as well as the RIGHT function. The SUBSTRING function version is likely to be the fastest, followed by the RIGHT version then the STUFF version.

    DECLARE @value varchar(20)

    SELECT @value = 'ks105'

    SELECT STUFF(@value, 1, 2, '') AS [Stuff],

    SUBSTRING(@value, 3, 18) AS [Substring],

    RIGHT(@value, LEN(@value) - 2) AS [Right]

Viewing 3 posts - 1 through 2 (of 2 total)

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