Urgent Help Required

Question

Urgent Help Required

Harshen Parikh

Grasshopper

Points: 12
More actions
July 22, 2005 at 12:47 am

#110786

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

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

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Harshen Parikh Grasshopper Points: 12 More actions · Answer 2

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