UNC Path - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

UNC Path

Author

Topic

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-11-05 : 18:12:24

Hi all I have a question I have a ADP access in the front SQL server as the engine. We scann our files and hyperlink them to adobe, the folders that store the scanned files have a space in between them and we are trying to change that into _ for instance so instead of spaces we will put a _ in between the spaces. there is a total of 10000 records the path is locaed inside of the SQL table, is there a way to change the actual folder and the path without getting an error message saying path can not be found??

\\gcsql\database\Backgrounds\SCANNED ACROBAT DOCUMENTS (BACKGROUNDS)\SCANNED ACTIVE FILES\0011.pdf

and change it too

\\gcsql\database\Backgrounds\SCANNED_ACROBAT_DOCUMENTS_BACKGROUNDS\SCANNED_ACTIVE_FILES\0011.pdf

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-05 : 18:16:48

UPDATE Table1
SET Col1 = REPLACE(REPLACE(REPLACE(Col1, ')', ''), '(', ''), ' ', '_')
WHERE Col1 LIKE '%[( )]%'

E 12°55'05.63"
N 56°04'39.26"

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-11-05 : 18:39:14

ok let make sure I do this correctly.
UPDATE Revised_MainTable
SET I/RDocument = REPLACE(REPLACE(REPLACE(I/RDocument, ')', ''), '(', ''), ' ', '_')
WHERE I/RDocument LIKE '%[( )]%'
is that what you mean or am i way off?

I'm looking at the path in the table and one is like this

Scanned Report#\\GCSQL\Database\Reports\Incident Reports\IR's 2001\01-0189.pdf#

the other is like this

Scanned Report#\\Gcsql\DataBase\Reports\Exclusion\ExclusionDocuments\1 Year Exclusions\CASTELLANOS, JAMIE 1 YEAR.pdf#

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-11-05 : 19:12:34

It worked great i just had to change the datatype. Thank you so much Peso

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-05 : 19:22:04

can't do string functions with ntext, you have to cast it as varchar(max)


UPDATE Revised_MainTable
SET [I/RDocument] = REPLACE(REPLACE(REPLACE(cast([I/RDocument] as varchar(max)), ')', ''), '(', ''), ' ', '_')
WHERE [I/RDocument] LIKE '%[( )]%'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-06 : 01:39:39

Paths cannot be longer than 256 characters anyway, so why are you using NTEXT as datatype, when NVARCHAR(260) would suffice?

E 12°55'05.63"
N 56°04'39.26"

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-11-06 : 11:42:12

I changed it over to NVARCHAR(max), at first I thought ntext since I was still learning now I will change it over nvarchar works much better

I have another question what about the actual scanned files, the users scan the files and when they save them they save them with spaces. Is there a scrpt to change that in the actual folder where the scanned files reside??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-06 : 11:44:35

max is also reqd as suggested by Peso as path will always be not longer than 256 characters.

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-11-06 : 12:06:18

ok thanks guys I appreciate the help

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-06 : 13:47:32

quote:
Originally posted by muzzettemm

I changed it over to NVARCHAR(max), at first I thought ntext since I was still learning now I will change it over nvarchar works much better

I have another question what about the actual scanned files, the users scan the files and when they save them they save them with spaces. Is there a scrpt to change that in the actual folder where the scanned files reside??

Perl script is best at this kind of stuff.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources