January 18, 2006 at 10:04 am
Need T-SQL to count up the occurrences of a given, arbitrary value listed within an NVARCHAR(255) defined column.
In the following example of data housed in a column called: USER_QUERY nvarchar(255) -- I need the value and count of the value stored AFTER the 3rd period for those strings containing the nested value '&track':
ctim=4.16.57.6334&track =3
ctim=91.330.1138.2134&track=41
ctim=8.26.105.7353&track=172
ctim=8.26.105.7353&track=168
ctim=8.26.105.7353&track=169
ctim=8.26.105.12888&track=1
ctim=8.22.83.7353&track=169
ctim=8.22.83.7353&track=172
ctim=89.328.1108.13735&track=60
ctim=89.328.1108.13735&track=61
January 18, 2006 at 10:22 am
You could try something like this where 'a' is the column containing your data
select SUBSTRING(a, (LEN(a) + 2 - CHARINDEX('=',REVERSE(a)) ),10), COUNT(a) AS Occurances from tblTest
where a like '%&track%'
group by SUBSTRING(a, (LEN(a) + 2 - CHARINDEX('=',REVERSE(a)) ),10)
January 18, 2006 at 10:27 am
I would use PATINDEX and SUBSTRING functions to isolate the strings and then group by your numbers. PATINDEX shows the first position of the character in the string.
For example,
select patindex('%.%','ctim=0.0.0.12888&track=5')
will return 7. You will have to use this function 3 times to get the position number of the third dot. Then get a position number for &. Use Substring function to form a temp table containing t your numbers where &track exsits in the string. This is easy: if it does not exist then PATINDEX will be 0 for the expression like:
select patindex('%&track%','ctim=0.0.0.12888=5')
Then just group and count
PS - Just saw a solution above - REVERSE will save you 3 iteration, good thought
Regards,Yelena Varsha
January 19, 2006 at 2:05 am
--I have to do quite a bit of this sort of work
--I use a generic function to return the nth part of a
--string delimited by whatever delimiter you specify
Create function dbo.ufsSplit --you need do this once only!!
(
@String varchar(8000),
@which int,
@Delimiter varchar(10) = ','
)
RETURNS varchar(8000) AS
BEGIN
Declare @ii int
Declare @Substring Varchar(8000)
select @ii=1, @Substring=''
WHILE @ii <= @which
BEGIN
IF (@String IS NULL OR @Delimiter IS NULL )
begin
select @Substring=''
BREAK
end
IF CHARINDEX(@Delimiter,@String) = 0
BEGIN
SELECT @subString = @string
select @String=''
END
else
begin
SELECT @subString = substring( @String, 1, CHARINDEX( @Delimiter, @String )-1)
select @String = substring( @String, CHARINDEX( @Delimiter, @String )+len(@delimiter),len(@String))
end
select @ii=@ii+1
END
RETURN (@subString)
END
go
-- so lets do the job properly and set up the test data
Set nocount on
declare @LogData table (userQuery varchar(255))
insert into @logdata(userquery) select 'ctim=0.0.0.12888&track=5'
insert into @logdata(userquery) select 'ctim=0.0.0.12888'
insert into @logdata(userquery) select 'ctim=4.16.57.6334&track=164'
insert into @logdata(userquery) select 'ctim=4.16.57.6334&track =3'
insert into @logdata(userquery) select 'ctim=91.330.1138.2134&track=41'
insert into @logdata(userquery) select 'ctim=8.26.105.7353&track=172'
insert into @logdata(userquery) select 'ctim=8.26.105.7353&track=168'
insert into @logdata(userquery) select 'ctim=8.26.105.7353&track=169'
insert into @logdata(userquery) select 'ctim=8.26.105.12888&track=1'
insert into @logdata(userquery) select 'ctim=8.22.83.7353&track=169'
insert into @logdata(userquery) select 'ctim=8.22.83.7353&track=172'
insert into @logdata(userquery) select 'ctim=8.22.83.7353'
insert into @logdata(userquery) select 'ctim=89.328.1108.13735&track=60'
insert into @logdata(userquery) select 'ctim=89.328.1108.13735&track=61'
--and the actual procedure is reasonably simple. It would require only a little mod
--to pick up the 'track' value too (but that is for you to try!)
Select 127.0.0.1=dbo.ufsSplit(dbo.ufssplit(userQuery,4,'.'),1,'&'),count(*) from @logdata
where userQuery like '%&track%' group by dbo.ufsSplit(dbo.ufssplit(userQuery,4,'.'),1,'&')
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply