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','-',''))
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','-',''))
Thanks Siva
ReplyDeleteThanks for sharing this.
ReplyDeletePost graduation education in germany
Excellent information with unique content and it is very useful to know about the information based on blogs.
ReplyDeleteErp In Chennai