getting distinct count of filename in sqlserver

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

General SQL Server Forums

New to SQL Server Programming

getting distinct count of filename in sqlserver

Author

Topic

subhaoviya
Posting Yak Master

135 Posts

Posted - 2011-01-09 : 23:29:44

Hi friends,

I need to get the unique files by considering the 6 octets and ignore all the characters after the 6th octet.

Example filenames:
1. In NFEBAK9.MQ.TF042.J72082.J1228.D0010:PGP0F6C6,
2. NFEBAK9.MQ.TF042.J72082.J1228.D0010:PGP0F6C6

While counting the files, need to consider uniqueness with only 6 octets
(1) NFEBAK9. (2) MQ. (3)TF042. (4) J72082. (5) J1228. (6) D0010

help me to form the query.

thanks
subha

subhaoviya
Posting Yak Master

135 Posts

Posted - 2011-01-10 : 04:10:39

declare @idx int
declare @slice varchar(8000)
declare @string varchar(1000)
set @string='nfebak0.hq.tek04.j88fls.j1228.f4740.hybrgr'
set @idx = charindex('.',@string)
if @idx!=0
set @slice = left(@String,@idx-1)
else
set @slice = @String
select @slice

by the above query am getting 1st octet result, without loop how to get the result like

'nfebak0.hq.tek04.j88fls.j1228.f4740' = expected file name (6 octet)

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-10 : 04:39:39

Need some clarification of the problem... are you trying to get the result with 6 slices having five dots. Can you let me know how many possible Octets are there in the given data. e.g. is it fixed (7) or having variation (7,8,9,...)

Any how, please find the below query this might help you. if required change it as per your requirements :)

declare @idx int
declare @slice varchar(8000),@string varchar(1000),@itr tinyint
set @itr=0
set @string='nfebak0.hq.tek04.j88fls.j1228.f4740.hybrgr'
set @slice=''
While @itr<=5
Begin
set @idx = charindex('.',@string)
if @idx!=0
Begin
set @slice = @slice +'.'+left(@String,@idx-1)
set @string=substring(@String,@idx+1,LEN(@string))
set @itr=@itr+1
End
else
Begin
set @slice = @String
End
End
Print substring(@slice,2,len(@slice))

Cheers!
MIK

subhaoviya
Posting Yak Master

135 Posts

Posted - 2011-01-10 : 05:15:17

select left('nfebak0.hq.tek04.j88fls.j1228.f4740.hybrgr',(CHARINDEX('.', 'nfebak0.hq.tek04.j88fls.j1228.f4740.hybrgr',
CHARINDEX('.', 'nfebak0.hq.tek04.j88fls.j1228.f4740.hybrgr',
CHARINDEX('.', 'nfebak0.hq.tek04.j88fls.j1228.f4740.hybrgr',
CHARINDEX('.', 'nfebak0.hq.tek04.j88fls.j1228.f4740.hybrgr',
CHARINDEX('.', 'nfebak0.hq.tek04.j88fls.j1228.f4740.hybrgr',
CHARINDEX('.','nfebak0.hq.tek04.j88fls.j1228.f4740.hybrgr') + 1) + 1)+1)+1)+1))-1)

this done the job

thanks friends

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-10 : 11:00:09

if always after 6 octet its : it will much easier. just do like

SELECT LEFT(col,CHARINDEX(':',col)-1) FROM table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources