Showing posts from July, 2015

SQL- count character in a string

There was requirement from my customer to count the special character available in particular field in the table and generate the report to show if this character is repeating more than 3 times in the field. I did not find any inbuilt function in SQL server and here is the solution I have provided. String - "45ghf-8293-89kj-kfji" SQL, we have function to retrieve the total length of the string using LEN function. if we are able to reduce the special character from the string and subtract this length with the actual length we will be getting the number of occurrence. SELECT '45ghf-8293-89kj-kfji',LEN('45ghf-8293-89kj-kfji'),LEN(REPLACE('45ghf-8293-89kj-kfji','-','')), LEN('45ghf-8293-89kj-kfji')- LEN(REPLACE('45ghf-8293-89kj-kfji','-',''))