​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking
h​ere


elmama
 Count number of sub-strings/letters in a string

This recent post in, of all places, a vbscript forum. The questioner was given plenty of vbscript answers but I thought that since the data was coming from a database that they should use the database to do it - but how?

Question

I need to write a script that can give me the count of a number in a field on a MSSQL 2000 database. Below is the field. It contains more than just one piece of info.This below is directly from one field in one table of my database. I need to count the occurrences of 20100 in each field like below in my table in my database. Any ideas would be more than I have to start with.

CODE30529201011907013:20:1700T0.1236201001907013:20:2000T0.1236201031907013:20:5200T0.1236JP

My Answer

​Use your database to do it. I don't know MSSQL but in Oracle it would be something like:-

SELECT (length(sourcefield) - length(Replace(sourcefield,search_str)))/length(search_str)
​from your_table.



Related to the above, here is a bit of SQL that will return a count of all the letters in a string.


   SELECT CHR(LEVEL+64) letter,LENGTH('AABCghdururAur') -  

   LENGTH(REPLACE('AABCghdururAur',CHR(LEVEL+64))) cnt  

   FROM dual

   CONNECT BY LEVEL < 27       

   UNION       

   SELECT CHR(LEVEL+96) letter,LENGTH('AABCghdururAur') -  

   LENGTH(REPLACE('AABCghdururAur',CHR(LEVEL+96))) cnt

   FROM dual

   CONNECT BY LEVEL < 27