Urgent Help Required

  • Hi! Everyone I am pretty new to Sql Server 2000 and mainly i am focused on Mobile Applications but recently i have been asked to get things done from database. I hope someone can help me out. Let me explain the situation

     

    Table:Mobilevideo

    Field: OriginalPath

     

    Example of Data:

     

    MobileVideo

    MobilevideoId             MediaAssetid                OriginalPAth

    1                               <Null>                         Lord.wmv

    2                              <Null>                         Hi_fi.wmv

    3                              <Null>                        SqlServer_01.wmv

     

    Second Table: Medialist

    Example of Data:

    Medialist

    id                    Media

    1                    /99/Lord.wmv

    2                  mms://microsoft.com.au/news/Hi_fi.wmv

    3                 news://abc.com.au/news/folder1/SqlServer_01.wmv

     

     

    Need to Produce following output

    Need to get Id on the basis of the Filename from mobilevideo

     

    Example

    Lord.wmv            Search Into MediaAsset and GetId and update that Id in to Mobilevideo.mediaassetid

     

    So Output

     

    MobilevideoId             MediaAssetid                OriginalPAth

    1                               1                                 Lord.wmv

    2                              2                                  Hi_fi.wmv

    3                              3                                SqlServer_01.wmv

     

    Well I am facing the Problem of Searching????? How to do that?????

     

    Can anyone explain me Please

    Thanks

     

     

  • Yuk, your problem here (if you hadn't already realised!) is stripping the filepath out of medialist.media to leave you with just the filename, which you can then join to MobileVideo.

    Here's some code which should work (untested):

    select v.MobileVideoID, l.ID MediaAssetID, v.OriginalPath

    from MobileVideo v

    inner join MediaList l

    on v.OriginalPath = (reverse(left(reverse(l.media), charindex('/', reverse(l.media))-1)))

    Explanation: reverse the media field, extract to the first occurence of '/' and then reverse it back.

    Regards

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Thanks Phil for your great and quick response. It is definately works out but because of we have inconsistent data it was giving an error like

    Invalid Length Passed for Substring Parameter.....

     

    Anyways I have found the solution which is similart to yours but modifed to suit my requirements.

    declare @table1 table (tid integer, files varchar(255))

    insert into @table1 (tid,files)

    select id, "afterHyph"= substring( media, len(media)-charindex('/', reverse( media))+2, 8000)

    from medialist

    and then updated my other table.

    But I really appreciate your reply. Thanks so much for that.

    Harshen

     

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

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