need to write a query that returns part of file path in field 1

[andreadd]

SQL 2008 R2 / Goldmine Premium 9

I have a text field that contains a file path

c:\docs\mydoc.doc

I need to write a query that drops off both ends so all you get is:

\docs
the length of these paths vary quite a bit.

substring(replace(cast(linkeddoc as varchar(max)), substring(cast(linkeddoc as varchar(max)), 1, charindex(':', linkeddoc)),''),1,

this is succesfully removing the drive letter. so i am now seeing:
\docs\mydoc.doc

my issue is dropping the filename -any ideas of how i can accomplish this? TIA for your help

 

[gmmastros]

What output would you expect for this?

[tt]
C:\docs\subfolder\anything.doc
[/tt]




-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[andreadd]

ORIGINAL: C:\docs\subfolder\anything.doc

WHAT IT NEEDS TO BE: \docs\subfolder\

 

[gmmastros]

Since you don't necessarily know how many sub folders there are, you're best bet would be to do something similar to what you have done to remove the drive letter. Only this time, you'll want to reverse the string first.

Run this in a query window to help get you started.

[tt]Select reverse('C:\docs\subfolder\anything.doc')[/tt]


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[andreadd]

so basically copy what i have above but start with REVERSE and swap ':' out for '\" ?

 

[gmmastros]

pretty much, yeah. You'll likely need to reverse it a couple time. Once for the charindex, again for the substring, etc...

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[andreadd]

thanks so much for your help.

i have in a different part of this query for another column this:

REVERSE(SUBSTRING(REVERSE(CAST(dbo.CONTSUPP.LINKEDDOC AS varchar(MAX))), 0, CHARINDEX('\',
REVERSE(CAST(dbo.CONTSUPP.LINKEDDOC AS varchar(MAX))), 1)))


i'm assuming i have to do something like this. this expression gives me just the filename. so i need one less reverse.

can you give a rough example? I tried copying what i had above and sql tells me that reverse needs 1 argument so obviously i am issing the boat

 

[gmmastros]

Do you have any situations where there are no folders? I mean... Data that looks like this?

[tt]Just_A_File.doc[/tt]


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[andreadd]

that is the only constant i do have going on

our folder sturcture looks like this:

\region\territory
so those 2 'steps' are for certain in all cases. after that there are more subfolders and sometimes files are pasted at the root of the territory folder.

so never ever just_a_file.doc

always at minimum: \region\territory\just_a_file.doc

 

[gmmastros]

Try this:

Select Case When PatIndex('%\%', dbo.CONTSUPP.LINKEDDOC) > 0 
            Then SubString(dbo.CONTSUPP.LINKEDDOC, 
		             PatIndex('%\%',dbo.CONTSUPP.LINKEDDOC), 
		             DataLength(dbo.CONTSUPP.LINKEDDOC) 
			            - CharIndex('\', Reverse(NullIf(Cast(dbo.CONTSUPP.LINKEDDOC as varchar(max)), '')))-1)
			Else '' End

If you have data with any slash, this code will return an empty string.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[andreadd]

would this barf if field was fer real text and not varchar?

 

[gmmastros]

would this barf if field was fer real text and not varchar?

Nope. It'll work just fine.

You will get incorrect results if the data type for the column is nText instead of text because the DataLength function will return a different value.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[andreadd]

was missing an ")" so i ran the query and i get msg back from angry SQL

Conversion failed when converting varchar value "\CA\CentralVal\MyGMDocs\Contract-Sears(sktn)-SearsAd" to data type int

 

[andreadd]

my bad i put the missing ")" in wrong place

what i get back is

\region\territory\just_a_file.doc

I am going to reread what you posted and retype

 

[andreadd]

everything else matches - i am still getting \region\territory\just_a_file.doc

 

[gmmastros]

I see what the problem is.

In a previous post, I said "You will get incorrect results if the data type for the column is nText instead of text because the DataLength function will return a different value. "

It seems pretty clear to me (know) that you are actually dealing with an NText column (not text). I just tested the code with ntext, and it does not return the correct data. Of course, this makes we wonder why you have ntext instead of text, or better yet... since we are talking about folders, why not something like varchar(1000)?

The difference between text and ntext (other than the N) is that ntext supports unicode characters, but also requires double the storage.

Since the original data appears to be ntext (unicode), then you should make sure that your code supports unicode, which is currently does not because you are converting it to varchar(max).

So... long story short, try this:

Select Case When PatIndex('%\%', dbo.CONTSUPP.LINKEDDOC) > 0 
            Then SubString(dbo.CONTSUPP.LINKEDDOC, 
                     PatIndex('%\%',dbo.CONTSUPP.LINKEDDOC), 
                     Len(Cast(dbo.CONTSUPP.LINKEDDOCas nvarchar(max)))
                        - CharIndex('\', Reverse(NullIf(Cast(dbo.CONTSUPP.LINKEDDOCas nvarchar(max)), '')))-1)
            Else '' End

I tested like this:

Declare @Temp Table(FileName nText)

Insert Into @Temp Values(N'c:\docs\mydoc.doc')
Insert Into @Temp Values(N'c:\docs\Ω\mydoc.doc')
Insert Into @Temp Values(N'c:\mydoc.doc')
Insert Into @Temp Values(N'')

Select Case When PatIndex('%\%', FileName) > 0 
            Then SubString(FileName, 
                     PatIndex('%\%',FileName), 
                     Len(Cast(FileName as nvarchar(max)))
                        - CharIndex('\', Reverse(NullIf(Cast(FileName as nvarchar(max)), '')))-1)
            Else '' End
From   @Temp

** Note, you can copy/paste the code above in to a query window to run it.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I missed a couple spaces in the code I posted above. This one should be correct.

Select Case When PatIndex('%\%', dbo.CONTSUPP.LINKEDDOC) > 0 
            Then SubString(dbo.CONTSUPP.LINKEDDOC, 
                     PatIndex('%\%',dbo.CONTSUPP.LINKEDDOC), 
                     Len(Cast(dbo.CONTSUPP.LINKEDDOC as nvarchar(max)))
                        - CharIndex('\', Reverse(NullIf(Cast(dbo.CONTSUPP.LINKEDDOC as nvarchar(max)), '')))-1)
            Else '' End

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[andreadd]

returns the same thing. i dont get it. I checked the properties of this field and it is text

this is actually the data that is stored:

~~SYNC=1
~~CREATETIME=200806051442
~~FILENAME=P:\SE\ftlauder\Lido Isles\Lido Isles Proposal.doc

so what the query is giving back is
\SE\ftlauder\Lido Isles\Lido Isles Proposal.doc

i see carriage return characters in the field and 2 on the end after the filename. dont know if that means anything

meaning this:

~~SYNC=1
~~CREATETIME=200806051442
~~FILENAME=P:\SE\ftlauder\Lido Isles\Lido Isles Proposal.doc

looks like this in sql:
~~SYNC=1char(10)~~CREATETIME=200806051442char(10)~~FILENAME=P:\SE\ftlauder\Lido Isles\Lido Isles Proposal.doc

 

[gmmastros]

My code assumed that the data started at beginning. I mean... I didn't know there was data before the name of the file.

Try this:

Select Case When PatIndex('%\%', dbo.CONTSUPP.LINKEDDOC ) > 0 
            Then SubString(dbo.CONTSUPP.LINKEDDOC , 
                     PatIndex('%\%',dbo.CONTSUPP.LINKEDDOC ), 
                     Len(Cast(dbo.CONTSUPP.LINKEDDOC  as nvarchar(max)))
			           - CharIndex('\', Reverse(NullIf(Cast(dbo.CONTSUPP.LINKEDDOC  as nvarchar(max)), '')))-1
			           - PatIndex('%\%',dbo.CONTSUPP.LINKEDDOC ) + 3
			        )
            Else '' End

Notice how quickly this got real ugly? If your data had been properly normalized, this would not have happened. If you don't know what [google]database normalization[/google] is, plead read up on it.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[andreadd]

george you are so freakin my hero!! MWAH! MWAH! MWAH! thank you thank you thank you


I am with you on database normalization but it is goldmine and i have a herd of salespeople. the 2 folder structure was as standardized as i was allowed. Goldmine too for some unknown really stupid reason stores notes as images - as you can imagine yanking them bad boys out is a real joy.

thanks again and have a great weekend!!