| Author |
Topic |
|
cplusplus
Aged Yak Warrior
567 Posts |
 Posted - 2009-10-26 : 16:24:59
|
| We have a problem with the data filenames, just the first part of teh filename which is 1654 to be replaced with teh appropriate number.D:\ProjectImages\DUS\DM\1654.4836-C-1013.pdfI have almost 20,000 rows to be modified just the filename first part.The have the filepath : "D:\ProjectImages\DUS\DM\1654.4836-C-1013.pdf"how can i replace the part 1654 with @Docid parameters value.Thank you very much for the help. |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-10-26 : 16:43:47
|
| well I'm going on the assumption you have 1654 all the time in your string. Use the replace function to fix it up based on your @DOCID parameter. here's an example.declare @myval varchar(10)set @myval='\1111.'select replace('D:\ProjectImages\DUS\DM\1654.4836-C-1013.pdf','\1654.',@myval)Mike"oh, that monkey is going to pay" |
 |
|
|
cplusplus
Aged Yak Warrior
567 Posts |
Posted - 2009-10-26 : 16:47:34
|
| 1654 is just an example it could be anything between "\" and "." |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-10-26 : 16:51:21
|
| is this 'D:\ProjectImages\DUS\DM\' always constant in the database or does that change up too?<><><><><><><><><><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
|
cplusplus
Aged Yak Warrior
567 Posts |
Posted - 2009-10-28 : 09:50:31
|
| I am sorry responding late.yes this part is same for this job: 'D:\ProjectImages\DUS\DM\'Thank you very much for the helpful info. |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-10-28 : 10:04:18
|
| [code]declare @d varchar(1000)select @d = 'D:\ProjectImages\DUS\DM\1654.4836-C-1013.pdf'select @d = replace(@d,substring(@d,(charindex('DM\',@d) + 3),charindex('.',@d) - (charindex('DM\',@d) + 3)),'urvalue')select @d[/code] |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-10-28 : 10:06:24
|
[code]declare @sample table( filepath varchar(50))insert into @sample select 'D:\ProjectImages\DUS\DM\1654.4836-C-1013.pdf'insert into @sample select 'D:\ProjectImages\DUS\DM\.4836-C-1013.pdf'insert into @sample select 'D:\ProjectImages\DUS\DM\1234567.4836-C-1013.pdf'declare @docid varchar(10)select @docid = '8888'select filepath, slash, dot, new_filepath = stuff(filepath, slash + 1, dot - slash -1, @docid)from( select filepath, slash, dot = charindex('.', filepath, slash + 1) from ( select filepath, slash = len(filepath) - charindex('\', reverse(filepath)) + 1 from @sample ) s) s/*filepath slash dot new_filepath-------------------------------------------------- ----------- ----------- --------------------------------------------D:\ProjectImages\DUS\DM\1654.4836-C-1013.pdf 24 29 D:\ProjectImages\DUS\DM\8888.4836-C-1013.pdfD:\ProjectImages\DUS\DM\.4836-C-1013.pdf 24 25 D:\ProjectImages\DUS\DM\8888.4836-C-1013.pdfD:\ProjectImages\DUS\DM\1234567.4836-C-1013.pdf 24 32 D:\ProjectImages\DUS\DM\8888.4836-C-1013.pdf(3 row(s) affected)*/[/code]
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-10-28 : 10:10:04
|
| [code]declare @myval varchar(10)set @myval='\1111.'select replace(file_path,substring(stuff(file_path,1,24,''),1,charindex('.',stuff(file_path,1,24,''))),@myval) from( select 'D:\ProjectImages\DUS\DM\1654.4836-C-1013.pdf' as file_path union all select 'D:\ProjectImages\DUS\DM\902873.78456-C-15013.pdf') as t[/code]MadhivananFailing to plan is Planning to fail |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-10-28 : 10:10:48
|
 MadhivananFailing to plan is Planning to fail |
|
|
|
cplusplus
Aged Yak Warrior
567 Posts |
Posted - 2009-10-28 : 10:18:22
|
| Wonderful.Thank you very much for the help Vijay. |
|
|
|
|
String functions