cancel
Showing results for 
Search instead for 
Did you mean: 

Query needed

Former Member
0 Kudos

Hi Experts,

I am in need of a Query

i have a table with different columns

-


id | name | date | path

-


10 | xyz | 10.12.2008 | C:\system\new\hello.pdf

20 | abc | 25.04.2008 | C:\data\old\one.jpg

and so on...

I want to diplay all of the fileds and in the last column that is the path column, I want the extention ( i.e PDF or JPG or so on) in a different new column and the file name (i.e hello or one so on) in a different new column and the path ( c:\system\ new or son on) in a different new column.

so it like all the 3 in 3 different columns..

Thanks & Regards,

Raj

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

The query is as

declare @path varchar(1000)
select @path = 'C:\data\old\one1.jpg'
select @path, substring(@path, charindex( '.', @path, 0) + 1, 10) as extension,
substring(@path, len(@path) - charindex('\', reverse(@path) ,0) + 2, 100  ) as filename,
substring(@path, 0, len(@path) - charindex('\', reverse(@path) ,0) + 1  ) as directory

Former Member
0 Kudos

Hi,

Thank you for your reply.

however in the Filename column the extenstion is also getting displayed, any idea how we can remove the extention and display only the file name...

many thanks in advace

Former Member
0 Kudos
declare @path varchar(1000)
select @path = 'C:\data\old\one1.jpg'
select @path, substring(@path, charindex( '.', @path, 0) + 1, 10) as extension,
substring(
substring(@path, len(@path) - charindex('\', reverse(@path) ,0) + 2, 100  )
, 1, len(substring(@path, len(@path) - charindex('\', reverse(@path) ,0) + 2, 100  )) - len(substring(@path, charindex( '.', @path, 0) + 1, 10)) - 1 )
 as filename,
substring(@path, 0, len(@path) - charindex('\', reverse(@path) ,0) + 1  ) as directory
Former Member
0 Kudos

Hi Everyone,

I dont want to be a spoilsport but the above query would fail for extension if there were 2 or more dots(.) in the folder name like C:\ab.dc\ and so on because windows accepts . in a folder name.

So i suggest that extension be got like this


Select Right(@path,4)

HTH

Ram

P.S. I had this problem before and still searching for solution.

Former Member
0 Kudos

ok, you are true, i didnt noticed this

when you will change the query as

declare @path varchar(1000)
select @path = 'C:\data\o.ld\one1.jpg'
select @path,

substring(@path, len(@path) - charindex('.', reverse(@path) ,0) + 2, 100  ) as extension
, 

substring(
substring(@path, len(@path) - charindex('\', reverse(@path) ,0) + 2, 100  )
, 1, len(substring(@path, len(@path) - charindex('\', reverse(@path) ,0) + 2, 100  )) - len(substring(@path, len(@path) - charindex('.', reverse(@path) ,0) + 2, 100  ) ) - 1 )
 as filename

,
substring(@path, 0, len(@path) - charindex('\', reverse(@path) ,0) + 1  ) as directory

it will works in your case.

Former Member
0 Kudos

Thanks mate

It does work.And i have been looking for it,for a while.

Former Member
0 Kudos

thank you... it was really helpfull

Answers (3)

Answers (3)

0 Kudos

Hi,

Please use 'substring' function while writing the sql query. It is very easy. You can learn it over internet. Just open google and search SQL + Substring

Pardeep

Former Member
0 Kudos

Check out this link of Microsoft to get help of SQL Server Functions

[http://msdn.microsoft.com/hi-in/library/ms174318(en-us).aspx]

Serch for string functions CHARINDEX, SUBSTRING, LEFT, RIGHT to do the query

former_member187989
Active Contributor
0 Kudos

raj,

make it clear question and

also from which table name?

Jeyakanthan

Former Member
0 Kudos

I don't understand !

Former Member
0 Kudos

ok You see 4 different coulums

ID , Name, date and Path

in the Path colum we have a Path with path, file name at file extention at the end.

from the Path column

I want the file extention in a seperate new column the file name in seperate new colum and the path in seperate new coulmn.