Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
SQL Count occurrences of character in string. (Read 13734 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
SQL Count occurrences of character in string.
21.11.06 at 10:58:58
 
SQL Count occurrences of character in string.
 
I needed to count the occurrences of a certain character in a string. I found the following solution in MySQL. The same technique will work for other databases as well but the syntax may be slightly different.
 
I have a table with a column in which I am storing IP addresses. Some of these IP addresses are not correct. They only have 3 sets of numbers and not 4.  
 
To find the rows I decided to count the number of full stops (.) in the IP address. If there are 3 then the IP address is ok. If there are less than 3, the IP address is invalid.
 
To count the number of spaces I am comparing the length of the unmodified IP address with the lengh of the IP address in which I am replacing the full stops with an empty string:
[tt]select *
from mytable
where length(ip) - length( replace(ip, '.' , '' ) ) < 3
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1