Talend ETL - How to remove special characters in the string

Talend allows us to define each data lake as a data source and then develop processes to combine and filter data from those sources to produce new products (reports, new data, spreadsheets, etc.) All this is done using a diagrammatic interface based on Eclipse.

tMap Component Transformation 

The tMap component is part of the Processing family of components. tMap is one of the core components and is primarily used for mapping input data to output data, that is, mapping one Schema to another.
The Map Editor allows us to enter a Mapping Expression for each of the columns in each output Schema.

As well as performing mapping functions, tMap may also be used to Join multiple inputs, and to write multiple outputs. Additionally, we can Filter data within the tMap component. We'll cover these features in a later article.

If you want to remove any special characters from the input string/text then you can achieve this by using a tMap component. tMap Component Transformation comes with two replace() and replaceAll() function where replaceAll() works with regular expressions, replace() works with CharSequence. You have to take care of replace function in tMap components. To use tMap with the following expression to get the expected result:

To remove all non-digit characters from a string

row1.inputField.replaceAll("\\D", "")

To remove all digits characters from a string

row1.inputField.replaceAll("[0-9]", "")

To remove all digits characters from a string

row1.inputField.replaceAll("*,&;!", "")

To remove all digits characters from a string

row1.inputField.replace("??", "")

This will replace requested characters by nothing. If you want to learn the whole process then you can watch our demo at YouTube Channel also -

SQL Server - How to get difference between two dates

If you are working as a SQL Professional then there is a lot of activities which are related to dates. You have to write SQL queries to find out the difference between two dates or need to get the next day, next month, next week, next quarter or next year to set some business logic. 

Business Scenario 

Suppose that you need to send an email alert to your customers to remind them to pay the invoice bill after 30 days of invoice generation as per their contract. Hence, you have to write an SQL based program to check all the unpaid invoices which comes under this conditions. 

Here, In your SQL based program (it may be a stored procedure which may be schedule to run on the daily basis on the defined schedule), you have to get the difference between the invoice date and current running date. 

If the difference result is matched with the contract with the customer then system will pull that invoice and send it to the customer as the email attachment. 

To full-fill the above business needs, you have to use some defined SQL Date related functions such as -

DateDIFF(interval, date1, date2) is used to get the differences between two dates and return numeric values such as difference in years, months, quarters, days, weeks, hours, minutes, seconds, milliseconds and nanoseconds etc. 

The interval of time is used to calculate the difference between date1 and date2. It can be one of the following values:

Value (any one of)	Explanation
year, yyyy, yy	Year interval
quarter, qq, q	Quarter interval
month, mm, m	Month interval
dayofyear	Day of year interval
day, dy, y	Day interval
week, ww, wk	Week interval
weekday, dw, w	Weekday interval
hour, hh	Hour interval
minute, mi, n	Minute interval
second, ss, s	Second interval
millisecond, ms	Millisecond interval

Examples

---- Difference in years SELECT DATEDIFF(year, '2012/04/28', '2014/04/28'); Result: 2 ---- Difference in months SELECT DATEDIFF(month, '2014/01/01', '2014/04/28'); Result: 3 ---- Difference in days SELECT DATEDIFF(day, '2014/01/01', '2014/04/28'); Result: 117 ---- Difference in hours SELECT DATEDIFF(hour, '2014/04/28 08:00', '2014/04/28 10:45'); Result: 2 ---- Difference in minutes SELECT DATEDIFF(minute, '2014/04/28 08:00', '2014/04/28 10:45'); Result: 165

To watch a demo video, please visit our YouTube channel -

Hope you enjoyed and found this essay useful. Please leave your questions and suggestions in the comments section. Many thanks for reading! 😊

Your support is greatly appreciated! If you found this article valuable, don’t forget to clap👏, follow✌️, and subscribe❤️💬🔔 to stay connected and receive more insightful content. Let’s grow and learn together!

⭐To learn more, please follow us —
http://www.sql-datatools.com
⭐To Learn more, please visit our YouTube channel at —
http://www.youtube.com/c/Sql-datatools
⭐To Learn more, please visit our Instagram account at —
https://www.instagram.com/asp.mukesh/
⭐To Learn more, please visit our twitter account at —
https://twitter.com/macxima
⭐To Learn more, please visit our Medium account at —
https://medium.com/@macxima

SQL Server - Treat Null Values in Count() function

As we know that the COUNT function can tell us the total number of rows returned in a result set (both NULL and non-NULL together but it is also depending on how it’s used). 

For example, 

• Using SELECT COUNT(*) or SELECT COUNT(1)  (which is what I prefer to use) will return the total of all records returned in the result set regardless of NULL values. 

This is because, we are not specifying any expression in the count function. So, this function is returning all the rows including null values also.

• Using COUNT (<Specific Column Name Here>) will count the number of non-NULL items in the specified column (NULL fields will be ignored).
• COUNT (<expression>) counts rows where the <expression> is not null.

Thus, we could find the number of NULL fields in the result set by subtracting the non-NULL fields from the Total fields. 

Note: In SQL Server, since the COUNT (and other aggregate functions) will ignore NULL values, we use the CASE to turn NULLs into values and values into NULLs

In the below example, we are creating a data-set with the help of common table expression and using the count function -

--This will return count of 2 ;WITH CTE     AS (     SELECT NULL AS Id     UNION ALL     SELECT 1 AS Id     )     SELECT COUNT(*) FROM CTE --This will return count of 1 ;WITH CTE     AS (     SELECT NULL AS Id     UNION ALL     SELECT 1 AS Id     )     SELECT COUNT (Id)  FROM  CTE

 So, it depends on you, how can you use count function in your SQL scripts.