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','-',''))



Comments

Post a Comment

Popular posts from this blog

Hadoop - Hive - Load data from csv/xls files

Microsoft BI Implementation - Cube back up and restore using XMLA command