Post reply

String Manipulation

  • January 4, 2011 at 2:43 pm

    #231657

    Hello Everyone and Happy New Year

    I am working with a file name that is stored in a table. I would like to use part of the string as the name and the Title to be shown on a web page. Every string has the same layout, but different length. At the end of the string there is the characters of 'large.jpg' or 'small.jpg' I want to remove that part of the string, and use the remaining string has the name or the title.

    String example - 'a-gift-from-the-heart-large.jpg'

    I want only this - 'a-gift-from-the-heart'

    Thank You in advance

    Andrew SQLDBA

  • January 4, 2011 at 3:08 pm

    #1269230

    something like

    REPLACE(<yourstring>,'-large.jpg','')

    Should do the trick


    Clear Sky SQL
    My Blog[/url]

  • January 4, 2011 at 3:09 pm

    #1269232

    Assuming you can ensure the data structure you could use omething like LEFT(StringValue,LEN(StringValue)-11)

    Both, 'large.jpg' and 'small.jpg' are 10 char long +1 char for the minus sign.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • January 4, 2011 at 3:22 pm

    #1269236

    Thanks Lutz

    That was it, worked perfectly. I did have to modify the length from 11 to 10.

    Andrew SQLDBA

  • January 4, 2011 at 3:31 pm

    #1269239

    AndrewSQLDBA (1/4/2011)

    Thanks Lutz

    That was it, worked perfectly. I did have to modify the length from 11 to 10.

    Andrew SQLDBA

    *Ouch* Can't even count such a small string manually anymore. Is that a sign or what? *Ouch*

    Should have used LEN('small.jpg') instead... :blush:

    @dave-3: I'm not sure if the REPLACE() is less costly compared to a LEFT(LEN()). Any reference for rough measures or don't you think there will be much of a difference?


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • January 4, 2011 at 3:38 pm

    #1269243

    LutzM (1/4/2011)

    @dave-3: I'm not sure if the REPLACE() is less costly compared to a LEFT(LEN()). Any reference for rough measures or don't you think there will be much of a difference?

    TBH , you would probably have to be doing millions of calculations before there would be any noticable difference either way, I would imagine LEFT is faster ( certainly cleaner code) . Its nothing to worry about in particular the cost of function (even aggregates , ever noticed that doing a SUM also invokes a COUNT ?).

    Also using a replace you would have to do it twice (large and small) 😉


    Clear Sky SQL
    My Blog[/url]

  • January 4, 2011 at 3:39 pm

    #1269245

    I am so sorry Dave

    Yes, your code worked perfect also.

    Thanks to both of you

    Andrew SQLDBA

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

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