SQL- count character in a string

By -
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

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

By -
Image
Current BI implementation require to deploy the cube in multiple location to make it available  locally in each location to improve the performance. Cube development happening in one location say X but since the company is having presence in around the world and so business users sits in different locations X, Y and Z. Internet connectivity between X, Y and Z are not good and accessing the cube deployed in X from Y and Z is very slow and it impact the end user productivity. To overcome this issue, cube has to be deployed in Y and Z locally and upload the connection in their local SharePoint as mentioned below.  Fig 1 Now let see how we can automate all these process from taking back up of the cube database to restore it in each location. Follow the below mentioned steps to automate back up and restore of cube database. - select the back up option by right click on the cube database to be backed up and generate the XMLA script after providing necessary proper
Read more

Databricks - incorrect header check

By -
This post I would like to show you how we can fix the problem of " Incorrect header check " received while fetching the data from hive table. Actual message " SparkException: Job aborted due to stage failure: Task 0 in stage 63.0 failed 4 times, most recent failure: Lost task 0.3 in stage 63.0 (TID 3506, 10.1.1.7, executor 28): java.io.IOException: incorrect header check.   at org.apache.hadoop.io.compress.zlib.ZlibDecompressor.inflateBytesDirect(Native Method)     at org.apache.hadoop.io.compress.zlib.ZlibDecompressor.decompress(ZlibDecompressor.java:221)     at org.apache.hadoop.io.compress.DecompressorStream.decompress(DecompressorStream.java:82)     at org.apache.hadoop.io.compress.DecompressorStream.read(DecompressorStream.java:76)     at java.io.InputStream.read(InputStream.java:101) " To better understand the scenario, let me explain how the data got loaded in the hive table. we are storing the data from source systems to RAW layer. H
Read more