If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Count for multiple occurrence of an expression in a string
You are awesome. This answers my question
Thanks a lot ! "Larry Daugherty" wrote: What comes redily to mind is a function that accepts two arguments: the string to analyze and the comparison character. Look in Help for Mid. The function would return an number of times the comparison character was found in the string as an integer. HTH -- -Larry- -- "chinky" wrote in message ... I want to count number of '.' (dots) in an expression e,g. asd.rty.de.com (answer = 3 dots at positions 4,8,11 respectively) . Instr functions gives only the first occurrence. Any help is appreciated Thanks Anupam |
#12
|
|||
|
|||
Count for multiple occurrence of an expression in a string
My apologies, I meant to say that I am using Access 2003
I am using it in SQL query, probably that is again my mistake. Need guidance on how to use it. Thanks "chinky" wrote: I want to count number of '.' (dots) in an expression e,g. asd.rty.de.com (answer = 3 dots at positions 4,8,11 respectively) . Instr functions gives only the first occurrence. Any help is appreciated Thanks Anupam |
#13
|
|||
|
|||
Count for multiple occurrence of an expression in a string
Duane Hookom wrote: I want to count number of '.' (dots) in an expression e,g. asd.rty.de.com (answer = 3 dots at positions 4,8,11 respectively) I think we need another possible solution. As this is the tabledesign group, what about a data-driven solution? If the OP has a Sequence table of integers (seq), parsing and counting is easy: SELECT T2.data_col, COUNT(S1.seq) AS tally FROM Test2 AS T2, [Sequence] AS S1 WHERE MID$(T2.data_col, S1.seq, 1) = '.' AND S1.seq BETWEEN 1 AND 20 GROUP BY T2.data_col Here's the VBA to fully reproduce: Sub CountChar() Dim cat Set cat = CreateObject("ADOX.Catalog") With cat .Create _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\DropMe.mdb" With .ActiveConnection ' Create Sequence table seq = 1 to 100 .Execute _ "CREATE TABLE [Sequence] ( seq INTEGER NOT" & _ " NULL PRIMARY KEY)" .Execute _ "INSERT INTO [Sequence] (seq) VALUES (1);" .Execute _ "INSERT INTO [Sequence] SELECT Units.nbr" & _ " + Tens.nbr AS seq FROM ( SELECT nbr FROM" & _ " ( SELECT 0 AS nbr FROM [Sequence] UNION" & _ " ALL SELECT 1 FROM [Sequence] UNION ALL" & _ " SELECT 2 FROM [Sequence] UNION ALL SELECT" & _ " 3 FROM [Sequence] UNION ALL SELECT 4 FROM" & _ " [Sequence] UNION ALL SELECT 5 FROM [Sequence]" & _ " UNION ALL SELECT 6 FROM [Sequence] UNION" & _ " ALL SELECT 7 FROM [Sequence] UNION ALL" & _ " SELECT 8 FROM [Sequence] UNION ALL SELECT" & _ " 9 FROM [Sequence] ) AS Digits ) AS Units," & _ " ( SELECT nbr * 10 AS nbr FROM ( SELECT" & _ " 0 AS nbr FROM [Sequence] UNION ALL SELECT" & _ " 1 FROM [Sequence] UNION ALL SELECT 2 FROM" & _ " [Sequence] UNION ALL SELECT 3 FROM [Sequence]" & _ " UNION ALL SELECT 4 FROM [Sequence] UNION" & _ " ALL SELECT 5 FROM [Sequence] UNION ALL" & _ " SELECT 6 FROM [Sequence] UNION ALL SELECT" & _ " 7 FROM [Sequence] UNION ALL SELECT 8 FROM" & _ " [Sequence] UNION ALL SELECT 9 FROM [Sequence]" & _ " ) AS Digits ) AS Tens WHERE Units.nbr +" & _ " Tens.nbr BETWEEN 2 AND 100 " ' Create test table .Execute _ "CREATE TABLE Test2 (data_col VARCHAR(20)" & _ " NOT NULL);" .Execute _ "INSERT INTO Test2 (data_col) VALUES" & _ " ('asd.rty.de.com');" ' Show results parsed Dim rs Set rs = .Execute( _ "SELECT T2.data_col, S1.seq AS pos, MID$(T2.data_col," & _ " S1.seq, 1) AS data_char FROM Test2 AS T2," & _ " [Sequence] AS S1 WHERE MID$(T2.data_col," & _ " S1.seq, 1) = '.' AND S1.seq BETWEEN 1 AND" & _ " 20") MsgBox rs.GetString rs.Close ' Show results tally Set rs = .Execute( _ "SELECT T2.data_col, COUNT(S1.seq) AS tally" & _ " FROM Test2 AS T2, [Sequence] AS S1 WHERE" & _ " MID$(T2.data_col, S1.seq, 1) = '.' AND" & _ " S1.seq BETWEEN 1 AND 20 GROUP BY T2.data_col") MsgBox rs.GetString rs.Close End With Set .ActiveConnection = Nothing End With End Sub Jamie. -- |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Count Occurances of String in a Column? | Bill | General Discussion | 4 | February 23rd, 2006 01:30 AM |
Print Search Listbox Results in Report Filter Preview ... HELP!!! | [email protected] | Setting Up & Running Reports | 0 | January 27th, 2006 08:34 PM |
Requested string function | Tom Ellison | Running & Setting Up Queries | 3 | December 15th, 2005 07:58 PM |
COUNT: Counting the number of times "X" comes up after the occurrence of "Y" | DrSues02 | General Discussion | 5 | August 3rd, 2004 06:35 AM |
Tough One, trying to count occurences of string based on string in separate column | RagDyer | Worksheet Functions | 2 | February 28th, 2004 03:10 AM |