Parse data for recurring value?
-
I have a field in a database that looks like this:
7104 7106 7112 7107 7132 7108 7150 7105 7110 7109 7111 7104 7106 7112 7107 7132 7108 7150 7105 7110 7109 7111 2
The careful observer will notice that the value 7104 appears twice.
Is there T-SQL that can parse that field for the number of times that 7104 shows up?
Thanks in advance?
-
declare @col varchar(2000)
select @col ='7104 7106 7112 7107 7132 7108 7150 7105 7110 7109 7111 7104 7106 7112 7107 7132 7108 7150 7105 7110 7109 7111 2'
select (len(@col)-len(replace(@col,'7104','')))/len('7104')
Cheers,
* Noel -
Noel, thanks for the prompt response. I should have mentioned that the string of numbers is variable, will this still work?
For example the very next entry might be
7104 7106 7112 7107 7132 7108 7150 7105 7110 7109 7111 2
-
Sure!
declare @col varchar(2000), @valueToSearchFor varchar(200)
select @col ='7104 7106 7112 7107 7132 7108 7150 7105 7110 7109 7111 7104 7106 7112 7107 7132 7108 7150 7105 7110 7109 7111 2'
,@valueToSearchFor ='7104'
select (len(@col)-len(replace(@col,@valueToSearchFor,'')))/len(@valueToSearchFor)
just don't use a zero length @valueToSearchFor
Cheers,
* Noel -
Please tolerate the newbie! The column in question is called "ivrs"
When I run this:
select (len(ivrs)-len(replace(ivrs,'7104','')))/len('7104')
where projectid = 2223
and systemid = 6
and calldatetime between
'2006-10-01 00:00:00' and '2006-10-31 23:59:59'
and attresult in (2)
I get this error message set:
Msg 207, Level 16, State 1, Line 2
Invalid column name 'projectid'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'systemid'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'calldatetime'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'calldatetime'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'attresult'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'ivrs'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'ivrs'.
-
You should not.
What you have is not data and storage of it is not a database.
To make it data convert all those strings into a column in some table (you may find necessary "split" function on this forum) and than manage data with database tools.
_____________
Code for TallyGenerator -
I'd love to Sergiy but it's not my data model. Is there a way to parse the string for the number of occurances?
-
Just curious, but I don't see a FROM in the SELECT, is something missing?
-
There is no data model. Neither yours nor anybody's else. Noone just biult one.
I answered your question, you just did not bother to read.
Lets try again:
convert string into single column table (you may find necessary "split" function on this forum) and than manage data with database tools.
_____________
Code for TallyGenerator -
thank you (boy I need to get some sleep!) and thank you for a kind response to a newbie
-
Here is your select statement:
select
(len(ivrs)-len(replace(ivrs,'7104','')))/len('7104')
where
projectid = 2223
and systemid = 6
and calldatetime between '2006-10-01 00:00:00' and '2006-10-31 23:59:59'
and attresult in (2)
Based on this, there should be a FROM clause with the TABLE name where the columns
ivrs, projectid, systemid, calldatetime, and attresult reside, like this:
select
(len(ivrs)-len(replace(ivrs,'7104','')))/len('7104')
from
dbo.sometable
where
projectid = 2223
and systemid = 6
and calldatetime between '2006-10-01 00:00:00' and '2006-10-31 23:59:59'
and attresult in (2)
If this is not the case, where is the data coming from, and where are you trying to put it?
-
Hi
Try this out (No Split/ No Parse)
DECLARE @col VARCHAR(2000)
DECLARE @FF VARCHAR(10)
SELECT @FF = '7104'
SELECT @col ='7104 7106 7112 7107 7132 7108 7150 7105 7110 7109 7111 7104 7106 7112 7107 7132 7108 7150 7105 7110 7109 7111 2'
SELECT CAST(DATALENGTH(@Col)-DATALENGTH(REPLACE(@Col,@FF,'')) AS INT) / LEN(@FF)
Ram
-
Sriram, I'm afraid, value '7104' is not given, you must to retrive duplicated values from the string inside your script.
And it may be not a single duplication in the string. You must discover and eliminate them all.
_____________
Code for TallyGenerator -
Hi Guy!
I think the correct T-sql statement is:
select (len(ivrs)-len(replace(ivrs,'7104','')))/len('7104') from <table name>
where projectid = 2223
and systemid = 6
and calldatetime between
'2006-10-01 00:00:00' and '2006-10-31 23:59:59'
and attresult in (2)
You should give a table name in select statement, in which you have a where clause.
Please check that out and get back to me guy.
Thanks
-
In case you haven't found a split function amongst the zillion sitting out there here is one that should take care of your problem. Then all you need to do is
select sval,count(*)
from dbo.split('7104 7106 7112 7107 7132 7108 7150 7105 7110 7109 7111 7104 7106 7112 7107 7132 7108 7150 7105 7110 7109 7111 2')
group by sval
having count(*)>1
create function split(@s varchar(1000)) returns @STAB table(sval varchar(4))
as
begin
declare @start int
declare @end int
if len(@s)>0
begin
set @start=1
set @end=charindex(' ',@s)
while @end>0
begin
insert into @STAB values(substring(@s,@start,@end-@start))
set @start=@end+1
set @end=charindex(' ',@s,@end+1)
end
insert into @STAB values(substring(@s,@start,len(@s)-@start+1))
end
return
end
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply